Forum Discussion

Maddy1010's avatar
Maddy1010
Brass Contributor
Apr 25, 2023

IF MATCH COMBINATION

Hello All,   In the attached sample file, Cell P3:R4, I would like to return the corresponding data relative to P1 and R2.  What would be a good IF/MATCH? combination to use to return the data alre...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Apr 25, 2023

    Maddy1010 

    =INDEX($B$3:$J$4,MATCH($O3,$A$3:$A$4,0),MATCH(1,(P$1=$B$2:$J$2)*(P$2=$B$1:$J$1),0))

    An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

NikolinoDE's avatar
NikolinoDE
Gold Contributor
Apr 25, 2023

=IF(AND(B1=P2,B2=P1,A3=O3),B3,"")
as an an example .-)

...insert in P3

Maddy1010's avatar
Maddy1010
Brass Contributor
Apr 25, 2023
Thanks NikolinoDE. I was hoping for a search range. For example: =if(and(B1:J1=P2,B2:J2=P1.....)
Any thoughts?
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Apr 25, 2023

    like this?
    =IF(AND(ISNUMBER(MATCH(P2,B1:J1,0)),ISNUMBER(MATCH(P1,B2:J2,0)),ISNUMBER(MATCH(O3,A3:A4,0))),INDEX(B3:J4,MATCH(O3,A3:A4,0),MATCH(P1,B2:J2,0)),"")

     

    ..insert in P3

    • Maddy1010's avatar
      Maddy1010
      Brass Contributor
      Apr 25, 2023
      almost there...not getting the result for columns Q and R. P turned out great though! Here's my formula: =IF(AND(ISNUMBER(MATCH(P$2,$B$1:$J$1,0)),ISNUMBER(MATCH(P$1,$B$2:$J$2,0)),ISNUMBER(MATCH($O3,$A$3:$A$4,0))),INDEX($B$3:$J$4,MATCH($O3,$A$3:$A$4,0),MATCH(P$1,$B$2:$J$2,0)),"")

Resources