View Full Version : [SOLVED] if and match formula

Mati44

07-18-2018, 09:13 AM

Why does this formula not work? Thanks.

=IF(ISNUMBER(MATCH($j1,$A6:$G6,0)),$j1,IF(ISNUMBER(MATCH($j2,$A6:$G6,0)),$j 2,IF(AND(ISNUMBER(MATCH($j1,$A6:$G6,0)),ISNUMBER(MATCH($j2,$A6:$G6,0))),"$j1,$j2","")))

Mati44

07-18-2018, 11:09 AM

ok. if I order the formula this way, it works.

=IF(AND(ISNUMBER(MATCH($j1,$A6:$G6,0)),ISNUMBER(MATCH($j2,$A6:$G6,0))),"$j1,$j2",IF(ISNUMBER(MATCH($j1,$A6:$G6,0)),$j1,IF(ISNUMBER(MATCH($j2,$A6:$G6,0)),$j 2,IF(AND(ISNUMBER(MATCH($j1,$A6:$G6,0)),"")

but I still have a problem with this part

"$j1,$j2"

instead of showing the values in j1 and j2 , I get $j1,$j2 literally. what should i do to fix this problem?

georgiboy

07-18-2018, 11:25 AM

What happens if you replace it with:

$j1,$j2

Instead of:

"$j1,$j2"

Mati44

07-18-2018, 11:26 AM

The formula doesn't work. I get: "you've entered too many arguments for this function" error.

georgiboy

07-18-2018, 11:35 AM

Should j1 be if true and j2 if false?

Mati44

07-18-2018, 11:39 AM

if j1 and j2 are in the $A6:$G6 range, the values of j1 and j2, should be placed in the cell, eg, "1,2".

if one of them in the range, then it should be that number in the cell.

Mati44

07-18-2018, 11:41 AM

So basicaly, I seek two numbers (a,b) in a range (A6:G6), if they are both in the range, then it should be like "a,b". if one of them in the range, then it should "a", or "b" in the target cell.

georgiboy

07-18-2018, 12:22 PM

How about:

=IF(AND(ISNUMBER(MATCH($J1,$A6:$G6,0)),ISNUMBER(MATCH($J2,$A6:$G6,0))),$J1&"," & $J2,IF(ISNUMBER(MATCH($J1,$A6:$G6,0)),$J1,IF(ISNUMBER(MATCH($J2,$A6:$G6,0 )),$J2,"Not found")))

Mati44

07-18-2018, 12:26 PM

Thank you very much. How couldn't I think of this? I forgot about &.

georgiboy

07-18-2018, 12:39 PM

No problem, another way maybe:

=IF(COUNTIF(A6:G6,J1)+COUNTIF(A6:G6,J2)>1,J1&","&J2,IF(COUNTIF(A6:G6,J1)>0,J1,IF(COUNTIF(A6:G6,J2)>0,J2,"Not found")))

Powered by vBulletin® Version 4.2.5 Copyright © 2021 vBulletin Solutions Inc. All rights reserved.