I think I'd build the formula not using the range name--just to get it working
ok.

=IF(ISBLANK(B383),"---",VLOOKUP($B383,fittings!$A:$C,3,FALSE))

It that works ok, then it's time to determine what PVC2 refers to. Maybe that's
where the error is.

What happened when you tried the other suggestion?



bduncan wrote:

I didn't have all three columns in the range. Thanks you, but
I changed the range and still have the same problem. In the evaluation it
says"IF(FALSE,#N/A,VLOOKUP9"239-503017 - BULKHEAD BOLTED FITTING 2"'
W/SIPHON",fittings!$a:$c,3,False)).

I'm guessing this is the problem in my formula but I don't know what is wrong.

"Bob Umlas, Excel MVP" wrote:

What's the range of PVC2? It needs to be at least 3 columns wide in order for
the "3" to make sense. or is the reference being returned a #REF! itself?

"bduncan" wrote:

I'm trying to run the following formula
"=IF(ISBLANK(B383),"---",VLOOKUP($B383,PVC2,3,FALSE))" and I keep getting the
error listed in the subject line.
We are doing inventory and on a couple of pages the people counting wanted
them by description instead of part number. I've put the parts on a
seperated worksheet and named the range for the parts to PVC2. I didn't what
to have to manually configure them jumping around. This would of been easy
except our accounting software kept the part numbers and descriptions in the
same cell. I actually exported 2 differents sheets from the software. one
listing every thing by part number and the other by description. I'm not
sure what is wrong. I've tried going through the evaluate formula and it
show's the #ref in there but not sure how to fix it.
239-503017 - BULKHEAD BOLTED FITTING 2" W/SIPHON #REF! 1.00


--

Dave Peterson