V Lookup 2nd Occurrence

[Rodney]

I was offered a solution to the above, and I think
my son has loaded a copy of DragonBallZ over the file on a floppy.
Apologies to the helpful person who replied.

I have tried unsuccessfully to find the post on Google.

I have 40,000 names in a list,
VLookup offers a value when the first occurence
is found in the criteria range.

I need to "skip" this value find the same name (2nd occurence) further down
the criteria range, and find the appropriate value there.

The formula I currently employ is:
=IF(ISERROR(VLOOKUP(EJ871,$A$2:$D$47,4,FALSE)),"",(VLOOKUP(EJ871,$A$2:$D$47,4,FALSE)))

This time if assistance is offered, I'll burn it to disk.

Thank you.

 

[Rowan]

This is the solution Frank Kabel gave you last time around:

try the array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(B1:B60000,SMALL(IF(A1:A60000="lookup_value",ROW(A1:A60000)),2))

Regards
Rowan

 

[Rodney]

Ripper! Rowan,
thank you very much indeed.
And of course to Mr. Frank Kabel.

Best regards
Rodney



| This is the solution Frank Kabel gave you last time around:
|
| try the array formula (entered with CTRL+SHIFT+ENTER):
| =INDEX(B1:B60000,SMALL(IF(A1:A60000="lookup_value",ROW(A1:A60000)),2))
|
| Regards
| Rowan
|
| "Rodney" wrote:
|
| >
| > I was offered a solution to the above, and I think
| > my son has loaded a copy of DragonBallZ over the file on a floppy.
| > Apologies to the helpful person who replied.
| >
| > I have tried unsuccessfully to find the post on Google.
| >
| > I have 40,000 names in a list,
| > VLookup offers a value when the first occurence
| > is found in the criteria range.
| >
| > I need to "skip" this value find the same name (2nd occurence) further down
| > the criteria range, and find the appropriate value there.
| >
| > The formula I currently employ is:
| > =IF(ISERROR(VLOOKUP(EJ871,$A$2:$D$47,4,FALSE)),"",(VLOOKUP(EJ871,$A$2:$D$47,4,FALSE)))
| >
| > This time if assistance is offered, I'll burn it to disk.
| >
| > Thank you.
| >
| >
| >
| >
| >
| >
| >
| >
| >

 

[Rodney]

Something is amiss here?

| This is the solution Frank Kabel gave you last time around:
| try the array formula (entered with CTRL+SHIFT+ENTER):
| =INDEX(B1:B60000,SMALL(IF(A1:A60000="lookup_value",ROW(A1:A60000)),2))

Is someone able to offer the above statement as a story
so I can nut out how the query is expressed please?
I am assuming INDEX is (reference)

My Criteria range is B2:B61490
the value of each criteria lies in cells E2:E61490
So my Criteria array is B2:E61490 ?

The list to which I need the value of the 2nd occurence
shown in the array, lies in cells B61495:B61956

I could not get Mr. Kabels' formula to work.

Thanks for any suggestions.
Rodney

 

[Andy Wiggins]

Which version of Excel are you using?

Here's an "easy" way to see what is happening.

1) Construct a demonstration database for yourself, for example:

Column A, Column B
aa,1
bb,2
bb,3
dd,4
bb,5

2) In cell C1 put the number 2
3) In cell D1 enter this version of the formula (remembering the
Ctrl+Shift+Enter): =INDEX(B1:B5,SMALL(IF(A1:A5=C2,ROW(B1:B5)),C1))
4) From the menu: Tools > Formula Auditing > Evaluate Formula, then keep
clicking on "Evaluate" to see what is happening.

--
Regards
-
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy

 

[Rodney]

Ah! Great Andy, thank you. (I have Excel97)
I have just mortgaged the house,
and purchased "Mastering Excel" 4th ed Thomas Chester, Richard Alden.

I am beginning to understand the obtuse ways that ROW and
INDEX work, by using small 12 cell examples.

Your suggestion is warmly welcomed,
and I look forward to the journey.

Best Regards
Rodney




| Which version of Excel are you using?
|
| Here's an "easy" way to see what is happening.
|
| 1) Construct a demonstration database for yourself, for example:
|
| Column A, Column B
| aa,1
| bb,2
| bb,3
| dd,4
| bb,5
|
| 2) In cell C1 put the number 2
| 3) In cell D1 enter this version of the formula (remembering the
| Ctrl+Shift+Enter): =INDEX(B1:B5,SMALL(IF(A1:A5=C2,ROW(B1:B5)),C1))
| 4) From the menu: Tools > Formula Auditing > Evaluate Formula, then keep
| clicking on "Evaluate" to see what is happening.
|
| --
| Regards
| -
| Andy Wiggins FCCA
| www.BygSoftware.com
| Excel, Access and VBA Consultancy
|
|
| | >
| > Something is amiss here?
| >
| > | This is the solution Frank Kabel gave you last time around:
| > | try the array formula (entered with CTRL+SHIFT+ENTER):
| > | =INDEX(B1:B60000,SMALL(IF(A1:A60000="lookup_value",ROW(A1:A60000)),2))
| >
| > Is someone able to offer the above statement as a story
| > so I can nut out how the query is expressed please?
| > I am assuming INDEX is (reference)
| >
| > My Criteria range is B2:B61490
| > the value of each criteria lies in cells E2:E61490
| > So my Criteria array is B2:E61490 ?
| >
| > The list to which I need the value of the 2nd occurence
| > shown in the array, lies in cells B61495:B61956
| >
| > I could not get Mr. Kabels' formula to work.
| >
| > Thanks for any suggestions.
| > Rodney
| >
| >
| >
| >
| >
| >
|
|

 

[Andy Wiggins]

Sorry, I don't think Formula Auditing is available in 97. Perhaps you will
need to sell your body so you can afford to upgrade

--
Regards
-
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy

 

[Rodney]

I received a NUM error, Andy.
The array was successfully entered, the statement had
brackets added by Excel.

The "Auditing" is a little different in Excel97
but understood.

Would you recommend I upgrade to a later Version?

Thankyou.




| Which version of Excel are you using?
|
| Here's an "easy" way to see what is happening.
|
| 1) Construct a demonstration database for yourself, for example:
|
| Column A, Column B
| aa,1
| bb,2
| bb,3
| dd,4
| bb,5
|
| 2) In cell C1 put the number 2
| 3) In cell D1 enter this version of the formula (remembering the
| Ctrl+Shift+Enter): =INDEX(B1:B5,SMALL(IF(A1:A5=C2,ROW(B1:B5)),C1))
| 4) From the menu: Tools > Formula Auditing > Evaluate Formula, then keep
| clicking on "Evaluate" to see what is happening.
|
| --
| Regards
| -
| Andy Wiggins FCCA
| www.BygSoftware.com
| Excel, Access and VBA Consultancy
|
|
| | >
| > Something is amiss here?
| >
| > | This is the solution Frank Kabel gave you last time around:
| > | try the array formula (entered with CTRL+SHIFT+ENTER):
| > | =INDEX(B1:B60000,SMALL(IF(A1:A60000="lookup_value",ROW(A1:A60000)),2))
| >
| > Is someone able to offer the above statement as a story
| > so I can nut out how the query is expressed please?
| > I am assuming INDEX is (reference)
| >
| > My Criteria range is B2:B61490
| > the value of each criteria lies in cells E2:E61490
| > So my Criteria array is B2:E61490 ?
| >
| > The list to which I need the value of the 2nd occurence
| > shown in the array, lies in cells B61495:B61956
| >
| > I could not get Mr. Kabels' formula to work.
| >
| > Thanks for any suggestions.
| > Rodney
| >
| >
| >
| >
| >
| >
|
|

 

[Andy Wiggins]

Sorry, forgot to tell you to put the entry you're looking for into cell C2.

Upgrade? Not if you're happy with 97.

--
Regards
-
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy