Formulas dont work

[comotoman]

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]

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.

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?

[vezerid]

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

[vezerid]

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

[Roger Govier]

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?
>
>
>
>