How can I lookup when match has more than one value?

  • Thread starter Thread starter Guest
  • Start date Start date
Try this:

...........A..........B..........C
1.......Bob.......x.........AA
2.......Sue.......x..........BB
3.......Bob.......z.........CC
4.......Sue.......a.........GG
5.......Tom......h.........FF

To lookup "Sue" and "a":

E1 = Sue
F1 = a

Array entered** :

=INDEX(C1:C5,MATCH(1,(A1:A5=E1)*(B1:B5=F1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Thank you so much! but i want to ask, what does 1 and 0 represents? and im a
little confused, why is it C1:C5?

Thanks you.
 
Got it!!! Thank you so much for your help! Your really the best!!!

last question, if im going to add additional condition, ill just use this
formula, right?

=INDEX(Data!C1:Data!C5,MATCH(1,(Data!A1:Data!A5=E1)*(Data!B1:Data!B5=F1)*(????????????),0))

is it correct?

Thank you so much!!!

=)
 
i still dont understand the 1 and the 0?...

MATCH(1,(A1:A5=E1)*(B1:B5=F1),0)

The 1 is the lookup_value and the 0 means we want to find an exact match of
the lookup_value.

Based on the sample I posted...

(A1:A5=E1)*(B1:B5=F1)

When both conditions are TRUE the result will be 1.

(Bob=Sue)*(x=a) = 0
(Sue=Sue)*(x=a) = 0
(Bob=Sue)*(z=a) = 0
(Sue=Sue)*(a=a) = 1
(Tom=Sue)*(h=a) = 0

This array of 1s and 0s make up the lookup_array.

MATCH(1,{0;0;0;1;0},0)

The result of MATCH is 4 and is passed to the INDEX function meaning we want
the 4th element of the indexed range C1:C5.

=INDEX(C1:C5,4)

=INDEX({"AA";"BB";"CC";"GG";"FF"},4)

GG is the 4th element of the indexed range so the result of the formula is
GG


E1 = Sue
F1 = a

=INDEX(C1:C5,MATCH(1,(A1:A5=E1)*(B1:B5=F1),0))

=GG
 
Yes, but you don't need to repeat the sheet name.

=INDEX(Data!C1:C5,MATCH(1,(Data!A1:A5=E1)*(Data!B1:B5=F1)*(????????????),0))
 
I am currently having to set up a excel spreadsheet for a ski lodge business
and there are several issues which are very confusing.
I am using a vlookup function and it is loooking up and selecting text, but
it is selecting the first text only because ther are several different values
with the same value, but i would rather it display more than one answer. is
there a variation of the vlookup function which shows two possible answers or
is there another function which is better suited??
 
This is very helpful to me also; however, I have a need to return every match
to a specific cell or in a continuous stream in one cell. The matches are
names that match a particular number. Is there a way to print in columns
specified, each match to the number being looked up?
 
I'm not sure how to word this exactly, but I have one sheet like this:

a b c d
red 12 11 9
blue 9 4 7
yellow 21 16 5
red 3 13 11

And I want to return the rows of just 'red' entries on another sheet,
without blank rows in the new sheet, like this:

a b c d
red 12 11 9
red 3 13 11

Can you help?
 
So I can help can you advise re the following:

1) Is there a fixed number of values you want to display, or does it vary?
2) How would you want them displayed? in a column? in a row?

Let me know the above and I'll post you the solution,

Jay
__
 
Hi,
May I request how to use vlookup or Index/Match combination to lookup the
drive utilization, for same hostname..

Server1 C:\ (2) 21478666240 17488568534 3990097706 81.42297263

Server1 D:\ (3) 2.9362E+11 68383018334 2.25237E+11 23.28963077


Server1 E:\ (4) 1.25325E+11 48705869576 76619192568 38.8636309

With Vlookup or Index/Match basic function use, I can get either the 1st
match or the last match, but not the middle one or the drive D. Any clue is
much appreciated.
Thankyou
 
Hi Valko,

I have something similar and I am just used to vlookup for one single value
to compare two columns so I get value in column2 returned.

Now I do have two columns plus a condition and three columns, and I would
like to get the value in column3 returned.

example:

ID name shs test results ID name shs

1234 Timo 13 D2 1225 Timo 20
1225 Timo 20 D3 1234 Timo 10


I thought vlookup can help if (A2+A3) match (E2+F2), then return the value
in column G.

Result: Cell D2 should get value "10" (G3) and Cell D3 should get the value
"20" (G2).

Thank you so much in advance.

Regards, Timo
 
Try something this:

=SUMPRODUCT(--(E$2:E$10=A2),--(F$2:F$10=B2),G$2:G$10)

Copy down as needed.
 
Hi,

I have a similar question on return sum of multiple vlookup values. Please
see the following array.

Item Date Amount
a 1-Sep 10
b 1-Sep 15
c 1-Sep 20
a 2-Sep 16
c 2-Sep 21
d 2-Sep 30
a 3-Sep 18
b 3-Sep 25

I want the function return the sum of values which 'Item' = 'a'. I used
=sum(vlookup(a, a2:c9, 3, false)) and it only returned the first value 10. I
expected to get (10+16+18) = 44. How can I achieve this result? Thanks.
 
=sumif(a:a,"B",c:c)

I changed the string to B just so there wouldn't be so many A's in the formula.
 
I had similar issue and surfed this website to find the solution but then I
trued used SumIF and it worked for me. For you it shoould be sumif(A1:A8, a,
C1:C8). Hope it works for you as well. Good luck.

Cheers,
Abid
 
Back
Top