PDA

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