IF MATCH COMBINATION

[Maddy1010]

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 already listed there?

UPDATE:   I adjusted a bit my question to be more precise.  Please see update excel file. 

@Hans Vogelaar

Thanks in advance!

[#2]

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

...insert in P3

[#3]

Thanks NikolinoDE. I was hoping for a search range. For example: =if(and(B1:J1=P2,B2:J2=P1.....)
Any thoughts?

[#4]

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

[#5]

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)),"")

[#6]

@Maddy1010 

works to me :)...insert a file example.

[#7]

Thanks NikolinoDE. Column P works. But in Q3, for example, the numbers should be 7.86 and Q4 should be 74.08, etc.

[#8 · Accepted solution]

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

[#9]

@Maddy1010 

My mistake in reasoning..was probably already in after-work mode :),

Mr. Quadruple_Pawn has the right formula, thank you :)

Here again the file with the correct formula from Mr. Quadruple_Pawn.

[#10]

@Maddy1010 In P3, use this as an alternative:

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

[#11]

This formula works like a charm! Thank you!