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

"Bob Umlas, Excel MVP" wrote:

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

"Amy" wrote:

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?!? :-)


"bpeltzer" wrote:

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.

"Amy" wrote:

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?