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" 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]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
>
>
>
>

[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
|
|
| "Rodney" <[email protected]> wrote in message
| news:[email protected]...
| >
| > 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" <[email protected]> wrote in message
news:O6v4%[email protected]...
> 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
> |
> |
> | "Rodney" <[email protected]> wrote in message
> | news:[email protected]...
> | >
> | > 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
> | >
> | >
> | >
> | >
> | >
> | >
> |
> |
>
>

[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
|
|
| "Rodney" <[email protected]> wrote in message
| news:[email protected]...
| >
| > 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


"Rodney" <[email protected]> wrote in message
news:%[email protected]...
>
> 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
> |
> |
> | "Rodney" <[email protected]> wrote in message
> | news:[email protected]...
> | >
> | > 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
> | >
> | >
> | >
> | >
> | >
> | >
> |
> |
>
>