Excel: Compare two values to another two values to return the adjacent value

0
votes

In excel, I am trying to compare two values to another two values that are in different columns. If they all match then it should copy the value on the adjacent column (In this case it's the third one), but I'm not finding any articles or websites explaining how to do it. You can find the formula that I am using below:

=IFERROR(VLOOKUP(AND(MATCH(A$2:A$22=F$1;A$2:B$22;0);MATCH(B$2:B$22=E2;A$2:B$22;0));A$2:B$22;3);"error")

Example Dataset

Note: The values in the first and second columns may differ (example: instead of the first floor I can have the ground floor). I need a formula that can be dynamic depending on the values that are inserted in those cells/columns/rows.

Can any of you help me?

2
excelexcel-formula

2 Answers

1
votes

You can use a LOOKUP construct like below in cell F2 to be copied down and across.

=IFERROR(LOOKUP(2,1/(($A$2:$A$22=$E2)*($B$2:$B$22=F$1)),$C$2:$C$22),"")

Note- Argument separator is comma for me which is semi-colon for you.

1
votes

In F2 try:

=IFERROR(LOOKUP(2,1/(($A:$A=$E2)*($B:$B=F$1)),$C:$C),"")

And drag down and right.

enter image description here

Or, if you don't care about false 0s:

=SUMIFS($C:$C,$A:$A,$E2,$B:$B,F$1)