#REF ERROR

[bduncan]

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

 

[Bob Umlas, Excel MVP]

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?

 

[Dave Peterson]

I'm betting that PVC2 has less than 3 columns.

Try:
Ctrl-g
type
pvc2
and hit enter

What is selected?

 

[bduncan]

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.

 

[Dave Peterson]

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]

Thanks for the help, I changed my range name and used the actual address. I
don't know if I made a mistake or that the error changed but the error is now
#N/A.

 

[Dave Peterson]

First, I didn't notice this in your original formula, but I think you have a
small mistake.

You used B383 in the =isblank() portion and $B383 in the =vlookup() portion. I
would expect that you would want that to be the same (absolution or
relative--but the same).

If you see #n/a, that means that there is no match in the first column of your
lookup range for the value in B383.

Debra Dalgleish has lots of notes on troubleshooting this kind of error:
http://contextures.com/xlFunctions02.html#Trouble

If your data doesn't really have a match and you want to hide that #n/a, you can
use format|Conditional formatting (white font on a white fill???).

Or you could change your formula:

=if($b383="","",if(isna(vlookup(...)),"Missing",vlookup(...)))

I like $b383="" better than =isblank($b383) since it's less typing.

You can change "Missing" to anything you want--including an empty string: ""

 

[bduncan]

Thanks for your help. I found my error. My information I was looking for
was in the 2nd column that I had selected not the first. Also It didn't make
a difference when I got the formula working if I had $b838 or B838, matching
or not matching.

Thanks for the help.

 

[Dave Peterson]

The lack of the $ in front of the B (in b838) won't make a difference until you
copy the cell with the formula into a cell in a different column.

But I'm not sure if you want the formula to adjust (use B838 if you do) or if
you want to always point at column B (use $b838).

But having a mixture of relative and absolute addresses looks like an error to
me.

Thanks for your help. I found my error. My information I was looking for
was in the 2nd column that I had selected not the first. Also It didn't make
a difference when I got the formula working if I had $b838 or B838, matching
or not matching.

Thanks for the help.