REF# error using VLOOKUP

A

Amy

I am trying to use the VLOOKUP fcn in my spreadsheet and am getting the REF#
error in the Table Array. I checked my named ranged, and they appear to be
okay. Any other suggestions of what may be wrong?
 
B

bpeltzer

What is the range, and what is the column index number (3rd argument) in your
vlookup? #REF can come up if the column index number exceeds the number of
columns in the table array.
 
A

Amy

What I have is a workbook that contains 200 sheets of data. I have created
and inserted a sheet named "Lookup" that I am trying to pull the data from.
Within that 'lookup" sheet, I have various ranges named. So, for example,
column A is CLIN, column B is WBS and column C is Total amt (see below).
CLIN1A contains cells B2:C10, CLIN2A contains cells B11:C20 and so on. I
have named my ranges by CLIN (CLIN1A, CLIN2A, etc). What I'm trying to do is
by using those named ranges, lookup the WBS number and drop the Total amt
into my other 200 sheets. I am using column index # 2.

CLIN WBS TOTAL AMT
0001A 01.01.03 472
0001A 01.01.04 5,364

To confuse things further, this function worked at one time. This workbook
that I have had a "Lookup" sheet in it, say 2 weeks ago. The problem is that
my Total amounts have changed, so what I tried to do was create a NEW
"Lookup" sheet, insert it into the workbook and delete the OLD "Lookup"
sheet. From what I can tell the new "lookup" is set up just like the old
"lookup", but for some reason the VLOOKUP formula now returns the REF# error
in the Table Array field of the formula box. Confused yet?!? :)
 
B

Bob Umlas, Excel MVP

It would help us if you showed us the EXACT VLOOKUP formula you're using and
the definitions of any defined names as well.
 
A

Amy

=VLOOKUP($B18,CLIN1A,2,FALSE)

Bob Umlas said:
It would help us if you showed us the EXACT VLOOKUP formula you're using and
the definitions of any defined names as well.
Click to expand...
 
C

Chip Pearson

Is CLIN1A defined to be at least two columns wide? It should be.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
K

Kevin Vaughn

I would double check CLIN1a if I were you. Perhaps it started out as Columns
B and C, but I believe you mentioned some deleting during the process. The
name itself may contain the REF error you are getting.
 
P

Pete_UK

Yes, if you have deleted the old lookup sheet, then the named ranges
which were on it will now have #REF in the Refers To box if you do
Insert | Name | Define. You will need to delete these old names and
re-instate them for the new lookup sheet.

Hope this helps.

Pete
 
Top