Matching Formula

  • Thread starter Thread starter matt
  • Start date Start date
M

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
 
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)*(D3:D7=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
 
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))
 
Back
Top