Sumproduct or something similar

[Sunvisor]

I have to find a row that has two specific matching criteria, the the row has the two matching criteria I need for it to return the value in column A

lets say I need to find a row where Column B serial number is ABC123 and Column C price is 43.09. With the row that has this matching criteria I need the value in Column A.


Thank you for any help!!

 

[T. Valko]

I have to find a row that has two specific matching criteria, the the row has the two matching criteria I need for it to return the value in column A

lets say I need to find a row where Column B serial number is ABC123 and Column C price is 43.09. With the row that has this matching criteria I need the value in Column A.


Thank you for any help!!

Try an array formula** like this...

Use cells to hold the criteria:

• E2 = ABC123
• F2 = 43.09

=INDEX(A2:A100,MATCH(1,IF(B2:B100=E2,IF(C2:C100=F2,1)),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.

 

[MrKowz]

Perhaps an array-entered INDEX/MATCH formula?

=INDEX(A1:A1000,MATCH(1,IF(B1:B1000="ABC123",IF(C1:C1000=43.09,1,"")),0))

Confirm entry with CTRL+SHIFT+ENTER to enter as an array formula. You will know it is entered properly when you see {brackets} around the formula.

 

[barry houdini]

This version doesn't need "array entering"

=LOOKUP(2,1/(B1:B1000="ABC123")/(C1:C1000=43.09),A1:A1000)