Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Vlookup to find Second INstance
I have a table of relationships - where an item can relate to one or
more items. Specifically, these are systems and their interfaces. For example, System A relates to (shares info/data) with System B which also has a relationship with System C. I want to generate something that looks like the previous statement. Column A would have System A (the lookup value) and Column B would have the result of a vlookup based on the value in column A. Column C would then vlookup from Column B and so on. The issue here is that System A relates to multiple systems. How can I rig a vlookup to find the second, third, fourth, etc, instances? Sample Data A - B - A A - C - B A - D - E A - E - B B - A - C B - E - H E - H |
#2
|
|||
|
|||
Hi!
You can't use Vlookup for multiple instances. Try something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B1:B10,SMALL(IF(A1:A10="A",ROW($1:$10)),Ins tance_Number)) Whe B1:B10 is the data to return A1:A10 is the range that contains the Lookup_Value "A". ROW($1:$10) is the SIZE of the range Instance_Number is the instance to look for Biff wrote in message ups.com... I have a table of relationships - where an item can relate to one or more items. Specifically, these are systems and their interfaces. For example, System A relates to (shares info/data) with System B which also has a relationship with System C. I want to generate something that looks like the previous statement. Column A would have System A (the lookup value) and Column B would have the result of a vlookup based on the value in column A. Column C would then vlookup from Column B and so on. The issue here is that System A relates to multiple systems. How can I rig a vlookup to find the second, third, fourth, etc, instances? Sample Data A - B - A A - C - B A - D - E A - E - B B - A - C B - E - H E - H |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP not returning results | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
How do I find the cell address of the 2nd largest of a set? | Excel Discussion (Misc queries) | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
Vlookup #N/A error due to formatting | Excel Worksheet Functions |