V-Lookup

  • Thread starter Thread starter Heather Gibbs
  • Start date Start date
H

Heather Gibbs

Hello,

I am trying to do a v-look up for the first time and am
having trouble converting my A column to a value. The
data that I am cross-referencing is 4 text characters
followed by 4 numbers, ex: ADAU1234. I'm not sure what
other information you might need, but please let me know
and I'll get it to you. Thank you in advance for any help
you can offer.

Heather
 
If you could post your formula and tell us how the table is set up?

Normally if you get an #N/A error that means that the lookup value
cannot find a match in the left most column of the table. That means that
the lookup value is not the same as any value from the lookup table.
For instance if you use

=VLOOKUP(A1,Sheet2!A2:C200,3,FALSE)

and that you have typed in ADAU1234 in A1, if you have imported
the table you might have a leading space so what looks like the same
value in fact is not the same value. One remedy then might be to use
something like

=VLOOKUP(A1,TRIM(Sheet2!A2:C200),3,FALSE)

entered with ctrl + shift & enter

now if you copied these values from the intranet or other webpage there
might be
invisible htlm characters and you might have to use

=VLOOKUP(A1,TRIM(SUBSTITUTE(Sheet2!A2:C200,CHAR(160),"")),3,FALSE)

also entered with ctrl + shift & enter

Now it is much better to remove the dissonances using for instance a macro
like this

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

than to make the formula more complex..
 
Back
Top