Home |
Search |
Today's Posts |
#1
|
|||
|
|||
V Lookup 2nd Occurrence
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. | | | | | | | | | |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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" wrote in message ... 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 |
#6
|
|||
|
|||
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" wrote in message | ... | | 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 | | | | | | | | |
#7
|
|||
|
|||
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" wrote in message ... 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" wrote in message | ... | | 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 | | | | | | | | |
#8
|
|||
|
|||
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" wrote in message | ... | | 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 | | | | | | | | |
#9
|
|||
|
|||
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" wrote in message ... 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" wrote in message | ... | | 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 | | | | | | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Lookup Lookup | Excel Worksheet Functions | |||
Lookup Function Problem | Excel Discussion (Misc queries) | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
lookup multiple occurrences of a value excel | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |