Thread
:
How can I lookup when match has more than one value?
View Single Post
#
51
May 5th, 2010, 09:50 AM posted to microsoft.public.excel.worksheet.functions
The Rimalaya
external usenet poster
Posts: 1
How can I lookup when match has more than one value?
"T. Valko" wrote:
If your data table is sorted or grouped together as is shown in your sample:
A2
10 = data table
F2 = lookup value = 34377007
G2 = instance number = 2
=INDEX(D2
10,MATCH(F2,A2:A10,0)+G2-1)
Result = 5313312
--
Biff
Microsoft Excel MVP
"Alfonso Valdes" Alfonso
wrote in message
...
Hi I have a huge list of data that has items and each item has different
specifications. For example:
36134008 R AA Z34116-1
36153004 R AA Z33021-1
34377007 P AA Z28031-1
34377007 P ZZ 5313312
34377007 P ZZ 4758766
36321027 P AA Z00000-1
36321027 P AA Z30918-1
36416003 P AA X32118-1
36421026 P ZZ 2394200
What I want to do is use a vlookup(34377007,$A$1:$B$8,4,FALSE)
The output that this vlookup will give me would be "Z28031-1" but in some
cases I want the information of the second row"5313312" or maybe the
third"4758766".
I have seen that there is explanations, and formulas that give you all the
info like this:
34377007 Z28031-1
5313312
4758766
But for the purpose of what i am doing I do not need all the values I
just
need one of them.
for example:
Same vlookup, but I want the formula to give me the info from the second
row
when it found the first value that match the vlookup
I do not know if exist a formula that makes this
vlookup(34377007,$A$1:$B$8,4(row2),FALSE)
="5313312"
36134008 R AA Z34116-1
36153004 R AA Z33021-1
34377007 P AA Z28031-1
34377007 P ZZ 5313312
34377007 P ZZ 4758766
36321027 P AA Z00000-1
36321027 P AA Z30918-1
36416003 P AA X32118-1
36421026 P ZZ 2394200
Same vlookup, but I want the formula to give me the info from the third
row
when it found the first value that match the vlookup.
Vlookup (34377007, $A$1:$B$8, 4(row3),FALSE)
="4758766"
36134008 R AA Z34116-1
36153004 R AA Z33021-1
34377007 P AA Z28031-1
34377007 P ZZ 5313312
34377007 P ZZ 4758766
36321027 P AA Z00000-1
36321027 P AA Z30918-1
36416003 P AA X32118-1
36421026 P ZZ 2394200
Cann't we do the same thing, if the data are not sorted... ??
The Rimalaya
View Public Profile
View message headers
Find all posts by The Rimalaya
Find all threads started by The Rimalaya