Search code examples
excelexcel-formulalookup

Find matching rows and return column value

I have 2 tables and I want to match the row in table A to the matching row in table B and return the Grade cell on the matched row from Table B.

Table A

P M D
0 3 1

Table B

P M D Grade
3 0 1 P
3 1 0 P
4 0 0 P
0 3 1 M
1 2 1 M
0 1 3 D

I've tried googling, and searching, but either don't understand it or it doesn't do what I want. I'm not an expert in Excel, any help greatly appreciated. Thanks Neil

Solution

  • Here is what I would do:

    enter image description here

    Formula in I2:

    =FILTER(D2:D7,BYROW(A2:C7=F2:H2,AND))

    Here I used what is called an ETA-LAMBDA syntax. If you so happen to use an old(er) version of Excel, you can try to apply MMULT() too since you are simply comparing arrays that are within the same order:

    =INDEX(D2:D7,MATCH(3,MMULT(N(A2:C7=F2:H2),{1;1;1}),0))