if and match formula

[Mati44]

Why does this formula not work? Thanks.

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

[Mati44]

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 )),$j2,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]

What happens if you replace it with:

$j1,$j2

Instead of:

"$j1,$j2"

[Mati44]

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

[georgiboy]

Should j1 be if true and j2 if false?

[Mati44]

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]

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]

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]

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

[georgiboy]

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