Jan 27 2023 06:53 AM
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?
Jan 27 2023 07:15 AM
=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.
Jan 30 2023 07:25 AM
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?
Jan 30 2023 07:53 AM
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.