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]

In my opinion, the easiest solution is to build yourself a simple lookup table listing the 12 months in English and in Dutch ...

 

[Bandito1]

That works

Not the solution i excpected but indeed an easy solution.

Thank you

 

[Alex Blakenburg]

Another thought, your list of dates are obviously dates but you are entering the lookup month as a text, is there an option to enter the lookup month as a date and then format it the same as your cells in your months list ?

PS: on my Excel to enter Feb I just need to type in 2/23, it will convert it to the 1st of Feb 2023.
If it wasn't a language issue I would type Feb 23 but some Dutch months have a different first 3 letters ie Mei for May. Maart for March

 

[nicnaws]

Hey there, Bandito1! I'm not sure if this is what you're looking for, but happy to help. I've color coded the text so you can see which parts of the formulas reference which cells. In cells A10 and A11, I typed in "1-January" and ended up with "1-Jan" in custom format, so I changed the format to text, and then retyped "1-January" in the same cell. Then I used a formula to extract "January" from "1-January". I've provided two different formula options, both of which will work for whatever data is entered for which month. You'd just have to update the range (array) and the month you're looking for as you add more data. I hope this helps.

 

[James006]

Once you have implemented your final solution, thanks for sharing it with the Forum.

Indeed, it could help out many future readers with a Foreign language set-up