V Lookup 2nd Occurrence

  • Thread starter Thread starter Rodney
  • Start date Start date
R

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.
 
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
 
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.
| >
| >
| >
| >
| >
| >
| >
| >
| >
 
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
 
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
 
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
| >
| >
| >
| >
| >
| >
|
|
 
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
 
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
| >
| >
| >
| >
| >
| >
|
|
 
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
 
Back
Top