Vlookup a Date on another sheet

[DavidMoran]

Hi
I am having trouble extracting a value relating to a date in another worksheet.

I am trying to lookup the value relating to 31/12/2010 as follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]31/12/2009
[/TD]
[TD]€6604[/TD]
[/TR]
[TR]
[TD]31/12/2010[/TD]
[TD]€7784[/TD]
[/TR]
[TR]
[TD]31/12/2011[/TD]
[TD]€8544[/TD]
[/TR]
</tbody>[/TABLE]

I have tried a simple vlookup but to no avail
Thanks

 

[FormR]

I have tried a simple vlookup but to no avail

Hi, what formula did you try exactly?

Excel 2013/2016
	A	B	C	D
1	A	B
2	31/12/2009	6604		7784
3	31/12/2010	7784
4	31/12/2011	8544
Sheet1

Cell Formulas
Range	Formula
D2	=VLOOKUP(DATE(2010,12,31),$A$2:$B$4,2,0)

 

[DavidMoran]

I am getting #REF ! with that formula

 

[FormR]

Hi, you said that you tried a simple VLOOKUP() formula - what did you try exactly?

I am getting [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF]#REF [/URL] ! with that formula

Do you have #REF ! errors in column B?

 

[DavidMoran]

on the other worksheet I converted the date to a number (31/12/2010 = 40543) and did a vlookup from that.

 

[FormR]

on the other worksheet I converted the date to a number (31/12/2010 = 40543) and did a vlookup from that.

Can you post the formula that you tried?

 

[DavidMoran]

=VLOOKUP(P3,'Input Sheet'!$D$26:$D$95,6,0)

P3 is the coverted date to number and Input sheet is the other worksheet where the range is

 

[FormR]

=VLOOKUP(P3,'Input Sheet'!$D$26:$D$95,6,0)

Your lookup table consists of only one column but you are attempting to retrieve data from the 6th column.

Maybe you need this instead:

=VLOOKUP(P3,'Input Sheet'!$D$26:$J$95,6,0)

 

[DavidMoran]

Oh man, a rookie mistake, thanks very much