Thread
:
How can I lookup when match has more than one value?
View Single Post
#
52
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:D10 = data table
F2 = lookup value = 34377007
G2 = instance number = 2
=INDEX(D2:D10,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... ??
Reply With Quote
The Rimalaya
View Public Profile
Find all posts by The Rimalaya