I am trying to do a vlookup function with data that is imported and
calculated from another cell, and it shows a result of #na.

c3 is formated to custom "m" to show the number of the month from cell
b3.

b3 is "October-04" c3 shows "10" d3 "job name"

b100 "10"
a100 =vlookup(b100,c3:f20,2,false)

result is "#na"

If I enter "10" into cell c3 with format set to general, then the
result shows the correct answer.

Is there a way to correct this?


--
comotoman
------------------------------------------------------------------------
comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292
View this thread: http://www.excelforum.com/showthread...hreadid=472676

Formatting a cell does not change its actual value, only what is
displayed. For a date, the actual content is the serial number of the
date, regardless of how it is formatted.

A simple way to do your job is to insert an auxiliary column BEFORE the
column with the dates (so now column C:C becomes the auxiliary column).
In this column enter =MONTH(D3) and copy.

Your formula now becomes: =VLOOKUP(val, C3:G20, 3, false)

HTH
Kostis Vezerides

Formatting a cell does not change its actual value, only what is
displayed. For a date, the actual content is the serial number of the
date, regardless of how it is formatted.

A simple way to do your job is to insert an auxiliary column BEFORE the
column with the dates (so now column C:C becomes the auxiliary column).
In this column enter =MONTH(D3) and copy.

Your formula now becomes: =VLOOKUP(val, C3:G20, 3, false)

HTH
Kostis Vezerides

Hi

The formatting of cell option "shows" 10, but the underlying value will
be 38261 or whatever the day of October 04 is.
In this case you need to use =MONTH(B3) which will return a numeric 10
and therefore be found in your vlookup formula.

Regards

Roger Govier



comotoman wrote:

I am trying to do a vlookup function with data that is imported and
calculated from another cell, and it shows a result of #na.

c3 is formated to custom "m" to show the number of the month from cell
b3.

b3 is "October-04" c3 shows "10" d3 "job name"

b100 "10"
a100 =vlookup(b100,c3:f20,2,false)

result is "#na"

If I enter "10" into cell c3 with format set to general, then the
result shows the correct answer.

Is there a way to correct this?

Sounds like C3 contains the same information as B3 which is the date
"October-04". No matter how you play with the format, Excel is still
reading the date that is contained in the cell. Even though the screen
shows a value of 10, Excel is not reading it as 10.

Try C3=MONTH(B3)

Hope it helps.


comotoman Wrote:
I am trying to do a vlookup function with data that is imported and
calculated from another cell, and it shows a result of #na.

c3 is formated to custom "m" to show the number of the month from cell
b3.

b3 is "October-04" c3 shows "10" d3 "job name"

b100 "10"
a100 =vlookup(b100,c3:f20,2,false)

result is "#na"

If I enter "10" into cell c3 with format set to general, then the
result shows the correct answer.

Is there a way to correct this?


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=472676