# Compair two pair of cells: function usage

Occasional Visitor

# Compair two pair of cells: function usage

Hello!

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

# Re: Compair two pair of cells: function usage

``=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.

# Re: Compair two pair of cells: function usage

@Quadruple_Pawn

Thanks for the reply, unfortunately it didn't work.
I uploaded the original file.

https://easyupload.io/9fgjjf
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?

# Re: Compair two pair of cells: function usage

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.