Matching Formula

[matt]

I am using this formula to return a name if it matches
=SUMPRODUCT((Sheet5!$O$6:$O$10=Sheet4!D$1)*(Sheet5!
$Q$6:$Q$10=Sheet4!$A20)*(Sheet5!$P$6:$P$10))

Basically it says if cell o6 in sheet 5 matches cell d1
AND cell Q6 in sheet 5 matches A20 in sheet 4 then return
what the value is in P6 on sheet 5. This formula works
great if I am returning numbers but now I am trying to
return a label(name) and it comes up with #value!. Any
ideas. Thank you

 

[Lance]

You can use SUMPRODUCT to return the location in the
table and INDEX to return the text value. Using the table
below this formula returns "B". Table is in C3:E7

Lance

=INDEX(E3:E7,SUMPRODUCT((C3:C7=2)*(D37=2)*MATCH
(E3:E7,E3:E7,0)))

C D E

1 1 A
2 2 B
3 3 C
4 4 D
5 5 E

 

[Aladin Akyurek]

Using SumProduct like that can be risky. Try...

=INDEX(Sheet5!$P$6:$P$10,MATCH(1,INDEX((Sheet5!$O$6:$O$10=Sheet4!D$1)*(Sheet
5!$Q$6:$Q$10=Sheet4!$A20),0,1),0))