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

[T. Valko]

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.

 

[Hijosdelongi]

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.

 

[Hijosdelongi]

IC.. Got it, C1:C5.. but i still dont understand the 1 and the 0?...

Thank you.

 

[Hijosdelongi]

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!C1ata!C5,MATCH(1,(Data!A1ata!A5=E1)*(Data!B1ata!B5=F1)*(????????????),0))

is it correct?

Thank you so much!!!

=)

 

[T. Valko]

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

 

[T. Valko]

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))

 

[findlay]

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??

 

[AMLL]

I have a flowup question: how can I return all of match up cells in my
spreadsheet?

 

[L Gillman]

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?

 

[ck555]

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?

 

[Jason]

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
__

 

[Ram]

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

 

[Timo]

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

 

[T. Valko]

Try something this:

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

Copy down as needed.

 

[Ray]

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.

 

[Dave Peterson]

=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.

 

[Baz]

Hi, I have the same query i.e. sum of multiple vlookups....I'd be very
interested to get advice

 

[Abid Malik]

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

 

[Excel-lent]

That should just be a simple sumif:
=SUMIF($A$1:$C$8,"a",$C$1:$C$8)

 

[Majal]

Hi Ray,

You may use the SUMIF function:

=SUMIF(A:A, "a", C:C)

Have a great day!