Column/Cell format for Vlookup

  • Thread starter Thread starter Glen Mettler
  • Start date Start date
G

Glen Mettler

I have a lookup formula that worked intermittently
=vlookup(K4,'WBS!$A2:$G$330,6,0)

Here is the problem:
If I change the lookup value (say from 411120 to 431130), I get an NA
although the numbers are there. If I go to the lookup sheet and copy and
paste the value into my worksheet, it worked.
Puzzling???

After making sure that the formats were the same -- I got the same result

On the advice of a friend, I used Data, Text to Columns... and noticed that
the setting was set to General. When I set the setting to Text everthing
worked.

Why? Why does Format, Cells, Text NOT work and DATA, Text to Columns...
Text DOES work?

Although I now have the problem fixed, I am currious to know why Format does
not work and Text to Columns does.

Anybody know??

Glen
 
... why Format does not work and Text to Columns does.

Unlike TTC, formatting does not change underlying values, only the display.
You probably had text numbers in the source table array and real numbers as
the lookup values (inputs). Another option to try the lookup / match
"as-is", i.e. w/o converting the source table array's WBS!$A2:$A$330 to real
numbers, would be something like:

=VLOOKUP(TEXT(K4,"000000"),WBS!$A2:$G$330,6,0)
 
Back
Top