If match doesn't exist #N/A

[BX]

Hi,
Is there anyway when using the match function to get a blank cell "" or 0 if the match doesn't exist. Currently, if a match doesn't exist I get #N/A.
Thanks,
BX

 

[Aladin Akyurek]

Options...

1]

X1:

=MATCH(LValue,1D_LTable,0)

Y1:

=IF(ISNA(X1),"",Y1)

2]

=IF(ISNUMBER(SETV(MATCH(LValue,1D_LTable,0))),"",GETV())

SETV/GETV requires the morefunc add-in.

 

[onlyadrafter]

Hello,

Try this formula, change the cell refs as required.

=IF(ISERROR(MATCH(D11,F12:F15,0)),"",MATCH(D11,F12:F15,0))

Any good?

 

[BX]

Thanks,
the second method worked perfectly!
Cheers.

 

[Aladin Akyurek]

Options...

1]

X1:

=MATCH(LValue,1D_LTable,0)

Y1:

=IF(ISNA(X1),"",Y1)

2]

=IF(ISNUMBER(SETV(MATCH(LValue,1D_LTable,0))),"",GETV())

SETV/GETV requires the morefunc add-in.

Correction: [2] should be...

=IF(ISNUMBER(SETV(MATCH(LValue,1D_LTable,0))),GETV(),"")

 

[Brian from Maui]

Hello,

Try this formula, change the cell refs as required.

=IF(ISERROR(MATCH(D11,F12:F15,0)),"",MATCH(D11,F12:F15,0))

Any good?

Here's a thread on ISERROR AND #N/A. Interesting reading.