Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup - Choose Which Occurence of Lookup value to bring back
I have a table where the lookup value appears multiple times .....
ie Table Looks like this Value Ref 3 A 5 Z 6 B 7 C 3 F 5 G With standard lookup functionality, If I looked-up value 3, I would bring back "A" I'd like a method where I can specify which occurence of "3" in the table it brings back data from. (ie to be able to do a lookup & specify to bring back data from the second record of "3" rather than the default first) Any Ideas ???? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup - Choose Which Occurence of Lookup value to bring back
You can add an occurence number to the lookup value and then use that to do
the lookup... To get the occurence no. you can type this formula in any column (say Col B) row 1 and copy down =COUNTIF($A$1:A1,A1) then you can can concatenate A & B in Col C and use that as the lookup col. VLOOKUP itself does not allow you to pick the occurence no. it returns the first match. "CHACHING" wrote: I have a table where the lookup value appears multiple times ..... ie Table Looks like this Value Ref 3 A 5 Z 6 B 7 C 3 F 5 G With standard lookup functionality, If I looked-up value 3, I would bring back "A" I'd like a method where I can specify which occurence of "3" in the table it brings back data from. (ie to be able to do a lookup & specify to bring back data from the second record of "3" rather than the default first) Any Ideas ???? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup - Choose Which Occurence of Lookup value to bring back
In article ,
CHACHING wrote: I have a table where the lookup value appears multiple times ..... ie Table Looks like this Value Ref 3 A 5 Z 6 B 7 C 3 F 5 G With standard lookup functionality, If I looked-up value 3, I would bring back "A" I'd like a method where I can specify which occurence of "3" in the table it brings back data from. (ie to be able to do a lookup & specify to bring back data from the second record of "3" rather than the default first) Any Ideas ???? Assumptions: A2:B7 contains the data D2 contains the lookup value E2 contains the occurrence of interest Formula: =INDEX(B2:B7,SMALL(IF(A2:A7=D2,ROW(A2:A7)-ROW(A2)+1),E2)) ....confirmed with CONTROL+SHIFT+ENTER. -- Domenic http://www.xl-central.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bring Back DBF! The GIS Industry uses it! | Excel Discussion (Misc queries) | |||
lookup to bring back multiple values | Excel Worksheet Functions | |||
How do I. VLOOKUP & BRING BACK THE CELL BELOW THE CELL WHICH IS FO | Excel Worksheet Functions | |||
Bring Back Qualified Sum from another WB | Excel Worksheet Functions | |||
Using VLOOKUP,what if I have more than one occurence of my Lookup. | Excel Worksheet Functions |