View Full Version : [SOLVED] if and match formula

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

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
instead of showing the values in j1 and j2 , I get $j1,$j2 literally. what should i do to fix this problem?

07-18-2018, 11:25 AM
What happens if you replace it with:


Instead of:


07-18-2018, 11:26 AM
The formula doesn't work. I get: "you've entered too many arguments for this function" error.

07-18-2018, 11:35 AM
Should j1 be if true and j2 if false?

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.

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.

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

07-18-2018, 12:26 PM
Thank you very much. How couldn't I think of this? I forgot about &.

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