SOLVED

Contributor

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

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

10 Replies

# Re: IF MATCH COMBINATION

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

...insert in P3

# Re: IF MATCH COMBINATION

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

# Re: IF MATCH COMBINATION

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

# Re: IF MATCH COMBINATION

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

# Re: IF MATCH COMBINATION

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

# Re: IF MATCH COMBINATION

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

best response confirmed by NikolinoDE (Respected Contributor)
Solution

# Re: IF MATCH COMBINATION

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

# Re: IF MATCH COMBINATION

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.

# Re: IF MATCH COMBINATION

@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))``

# Re: IF MATCH COMBINATION

This formula works like a charm! Thank you!