Lookup function

  • Thread starter Thread starter Greg Hadrych
  • Start date Start date
G

Greg Hadrych

After I create the lookup formula, I go to type in the
desired lookup criteria and the formula will not work. It
only responds with #N/A. If I copy and paste the criteria
from the lookup array, it will work correctly. I was
under the impression that the results will update every
time you change the criteria? Here is a break down of
what I have.

A1:B6951 contains he lookup information

E2 contains search criteria
F2 contains the formula "=VLOOKUP(E2,A2:B6951,2)".

If I type in for example 201207 into cell E2, I get #N/A
but if I copy cell As (which contains the same number -
201207) and then paste it into cell E2, the proper answer
is displayed. The formatting is the exact same in both.
Thank you in advanced for any help.
 
It's because what's in the table is not the same, probably a case of either
text in the table
while you are typing in a number. First try

=VLOOKUP(TEXT(A1,"@"),C2:D50,2,0)

if that doesn't work then you have either hidden spaces in the table

=VLOOKUP(TEXT(A1,"@"),TRIM(C2:D50),2,0)

entered with ctrl + shift & enter, if that doesn't work you probably have
trailing html characters, then use a macro

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

install the macro and run it on the table, that will work for the first
solution
as well

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Greg

It sound to me like the format is not the same, even if
you formatted both as number, if the first cell, the one
that when you copy and paste was text before you formated
it will not become number unless you reenter the formula.
which can be done fairly easy selecting the column with
the text numbers and doing DATA>TEXT TO COLUMNS > FINISH
this will effectively reenter all cells. since when you
copy and paste the cell it works fine I would think that
also the range A1:A6951 has the same format as the cell
you are copying and that's why it works... Make sure that
*ALL*, A1:A6951, E2 and the original cells that you copy
have the same format( be it number or text)

Cheers
Juan
 
It had to be that easy.... i have only been working on it
all morning. Thanks for the help
 
Back
Top