Vlookup problem

[Bandito1]

Hi all,

Im trying to retrieve data with Vlookup;

=VLOOKUP(F18;A11:C12;2;FALSE)

In F18 i typed "January".

It doesn't find any data; the months in Column A are made with;

=DATE($A$9;ROWS($9:9);1)

When i type there "January" it works.

I can't hard type "January" cause than other data will not be retrieved.
Anyone know how i can retrieve data with Vlookup based on month if i use a formula to created the month?

Tracker QA on the floor New.xlsm
	A	B	C
11	January	133	122
12	February	74	70
BMR data

Cell Formulas
Range	Formula
A11:A12	A11	=DATE($A$9,ROWS($9:9),1)
B11:B12	B11	=SUMPRODUCT(('Tracker QA on the Floor'!J:J>=A11)*('Tracker QA on the Floor'!J:J<=EOMONTH(A11,0)))
C11:C12	C11	=SUMPRODUCT(('Tracker QA on the Floor'!L:L>=A11)*('Tracker QA on the Floor'!L:L<=EOMONTH(A11,0)))

Tracker QA on the floor New.xlsm
	F
18	January
19	#N/A
BMR data

Cell Formulas
Range	Formula
F19	F19	=VLOOKUP(F18,A11:C12,2,FALSE)

 

[James006]

Hi,
You can test

=VLOOKUP(DATE($A$9,MONTH(1&F18),1),A11:C12,2)

 

[Bandito1]

Still gives #value error

In A9 is 2023, the current year

 

[James006]

Sorry ... just noticed you are using ; and not a comma as a formula separator...

=VLOOKUP(DATE($A$9;MONTH(1&F18);1),A11:C12;2)

 

[Bandito1]

Sorry for my late reply, i've been out for a while.

I still get the #Value error

Book2
	A	B	C
1	2023
2	Month
3	January	132	122
4	February	101	89
5
6	january	#VALUE!
Sheet1

Cell Formulas
Range	Formula
A3	A3	=DATE($A$1,ROWS(1:$1),1)
A4	A4	=DATE($A$1,ROWS(1:$2),1)
B6	B6	=VLOOKUP(DATE($A$1,MONTH(1&A6),1),A3:C4,2)

When i look into the calculation steps i see it goes wrong when it looks for the month.
It sees 1january wich is not in the sheet.

But when i change the formula to

=VLOOKUP(DATE($A$1;MONTH(B18);1);A11:C12;2)

It still results in #VALUE

Someone know what is going on?

It should look up the value in A6 in the range A3:C4.

The months are created with

=DATE($A$1;ROWS(1:$1);1)

wich makes it difficult

 

[Alex Blakenburg]

I have logged off for the night but try:

=VLOOKUP(DATEVALUE("1-"&A6&"-"&A1);A3:C4;2)/[CODE]

 

[Bandito1]

That gives also the #value

Tomorrow another day

 

[Alex Blakenburg]

What language is the default for your computer ?
If you type 1-January into a cell does it convert it to a date ?

 

[James006]

By the way ... have you made sure cell A6 has no spaces or blanks at the end ? ...

 

[Bandito1]

A6 has no spaces or blanks at the end

I think my default language is english (United states)
When i type 1-january it doesn't convert to date. It stays General.

When i press Evaluate