lookup returning #N/A
 

i have the formula,
=IF(F21="","",INDEX(Densities!O:P,MATCH(F21,Densit ies!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?

lookup returning #N/A
 

Hi Miree

I use if(iserrors) on all my vlookups?

Gordon...

"Miree" wrote:

i have the formula,
=IF(F21="","",INDEX(Densities!O:P,MATCH(F21,Densit ies!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?

lookup returning #N/A
 

Try this,

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

Mike

"Miree" wrote:

i have the formula,
=IF(F21="","",INDEX(Densities!O:P,MATCH(F21,Densit ies!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?

lookup returning #N/A
 

Another way is to use an IF(ISNA(MATCH(..)),"", .. viz.:
=IF(ISNA(MATCH(F21,Densities!O:O,0)),"",INDEX(Dens ities!O:P,MATCH(F21,Densities!O:O,0),2))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik
---
"Miree" wrote:
i have the formula,
=IF(F21="","",INDEX(Densities!O:P,MATCH(F21,Densit ies!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?

lookup returning #N/A
 

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(F 21,Densities!O:O,0))

Cell A2 Formula
=IF(A1<"",IF(F21="","",INDEX(Sheet2!O:P,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" wrote:

i have the formula,
=IF(F21="","",INDEX(Densities!O:P,MATCH(F21,Densit ies!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?

lookup returning #N/A
 

I have this formula working now
=IF(F21="","",IF(ISNA(MATCH(F21,Densities!O:O,0)), "",INDEX(Densities!O:P,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

"redeagle" wrote:

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(F 21,Densities!O:O,0))

Cell A2 Formula
=IF(A1<"",IF(F21="","",INDEX(Sheet2!O:P,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" wrote:

i have the formula,
=IF(F21="","",INDEX(Densities!O:P,MATCH(F21,Densit ies!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?