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 |
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 |
All times are GMT +1. The time now is 10:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com