I thought I understood the =if, vlookup formulae

  • Thread starter Thread starter bill
  • Start date Start date
B

bill

formula =if(trim(a6)="","",vlookup(A6,Scanner_table,2,0))
looks up in scanner_table column 2 & then places matching
data for whatever is in A6 in the appropriate column of
my worksheet.

I have now added an additional column (column 3) to
Scanner_table so why do I get answer #REF when i insert
formula
=if(trim(a6)="","",vlookup(A6,Scanner_table,3,0))?
I thought I had understood what Gary Thompson (Thanks
Gary) had written.
Bill
 
Frank
Thanks for interest
Scanner_table now has columns
A = Scanner Reference
B = Serial Number
C = MAC Address
Each column has 6 vertical cells
complete range is a4:c9
A3:c3 are column titles
Each cell is alpha numeric
Scanner Ref CA001 (CA002 CA003 etc.)
Serial number M1h28tu1
Mac Address is similar in format to Serial number

The formula worked fine before i inserted Column C

Hope this is what you mean by "Defined"
Thanks Bill
 
Hi

Frank asked about named range Scanner_table in your formula. Probably you
inserted a column, but didn't edit Scanner_table - so it contains only 2
columns - A and B.
 
To elaborate on Arvi's & Frank's comments, to see the definition of a range,
choose Insert / Name / Define, click on the range's name and have a look in
the "refers to box" down the bottom of the dialog box. You can edit its
definition here.

Cheers
JulieD
 
Such a simple mistake - I forgot to include the
additional column in the Range Name!!
I bet I'm not the only one
Thanks everyone
 
Back
Top