Compair two pair of cells: function usage

Occasional Visitor


I'm looking for a way to do the following:

-Compare a pair of cells on sheet A to another pair of cells on sheet B.
-If the pairs match (only one match will be possible), return the value from the column on sheet B corresponding to that matched pair.



Can somebody help me?

3 Replies


=INDEX('sheet B'!$C$3:$C$5,MATCH(1,('sheet B'!$A$3:$A$5='sheet A'!A3)*('sheet B'!$B$3:$B$5='sheet A'!B3),0))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

sheet A.JPGsheet B.JPG 


Thanks for the reply, unfortunately it didn't work.
I uploaded the original file. 
What I'm trying to do is:

-In sheet REDE COLETORA DE ESGOTO match the pair of cells on column B and C with the corresponding pair in sheet APOIO.
-In sheet REDE COLETORA DE ESGOTO column D return the value from sheet APOIO column D that corresponds to the matched pair.

Can you give it a go on the original spreadsheet?


Arrayformulas don't work with merged cells. You can remove the merged cells as shown in the screenshot. If you don't work with Office365 or Excel 2021 you have to enter the formula with ctrl+shift+enter. The formula then returns the expected result 56,275.


unmerge cells in sheet REDE COLETORA DE ESGOTO.JPG