lookup returning #N/A

[Miree]

i have the formula,
=IF(F21="","",INDEX(Densities!O,MATCH(F21,Densities!O:O,0),2))
This returns #N/A if the F21 string can not be found
It there anyway to get it to return 0 or empty insstead?

 

[Gordon]

Hi Miree

I use if(iserrors) on all my vlookups?

Gordon...

 

[Mike H]

Try this,

=IF(OR(F21="",COUNTIF(O:O,F21)=0),"",INDEX(Densities!O,MATCH(F21,Densities!O:O,0),2))

Mike

 

[Max]

Another way is to use an IF(ISNA(MATCH(..)),"", .. viz.:
=IF(ISNA(MATCH(F21,Densities!O:O,0)),"",INDEX(Densities!O,MATCH(F21,Densities!O:O,0),2))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik

 

[redeagle]

Hi Miree-

I think the problem is that the Match Function is hardcoded to return #N/A
if a match isn't found.

Maybe you could create another cell that does the Match function first by
using the ISERROR like Gordon suggested.

Cell A1 Formula
=IF(ISERROR(MATCH(F21,Densities!O:O,0)),"",MATCH(F21,Densities!O:O,0))

Cell A2 Formula
=IF(A1<>"",IF(F21="","",INDEX(Sheet2!O,MATCH(F21,Sheet2!O:O,0),2)),"")"

That is kind of a round about way of doing it but something to consider.
You could also tackle it in VBA if you wanted to go that route.

John

 

[Miree]

I have this formula working no
=IF(F21="","",IF(ISNA(MATCH(F21,Densities!O:O,0)),"",INDEX(Densities!O,MATCH(F21,Densities!O:O,0),2)))

BUT i am using a macro to enter it into cells so i can write over it again
if the number it returns is not what i want.

I am new to VBA so Can you maybe help me convert this to code, explain what
the code means because i wont use if i cant understand it.

Thank you