Forum Discussion

and_woox's avatar
and_woox
Copper Contributor
Jan 27, 2023

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 f...
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jan 27, 2023

and_woox 

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

 

  • Jefcoi1530's avatar
    Jefcoi1530
    Copper Contributor
    Jan 30, 2023

    OliverScheurich

    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?

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Jan 30, 2023

      Jefcoi1530 

      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.

       

       

Resources