Nested IF and Match issue

[andrewlau2881]

Hi all,

=IF(MATCH(K2,A3:A33,0),1,IF((MATCH(K2,B3:B33,0)),2,""))

I would like to search through columns A and B to match a name.

If that name is found then I would like to return 1 if it is in column A and 2 if it is in column B.

It returns a 1 if it is in column A. However, if it is in column B I get N/A. I have used very similar IF statments before with success but cannot get my head around why this one won't work.

All help appreciated.

 

[StephenCrump]

If it's not in column A, the first MATCH will return #N/A, and hence the formula will return #N/A.

One way to fix it: =IF(ISNUMBER(MATCH(K2,A3:A33,0)),1,IF(ISNUMBER(MATCH(K2,B3:B33,0)),2,""))

Or with Excel 365, you could say: =LET(m,MIN(IF(A2:B33=K2,{1,2})),IF(m=0,"",m))