# Vlookup Month and Year

#### RoganLogan

##### New Member
Hi Guys, This is probably pretty simple but I’m struggling to figure it out.

I have column of prices that are applicable to a column of months and years.

I also have to enter a date and time into cell F6 in the following format 05/04/2021 11:00:00. This format cant be changed.

I am trying to use a vlookup to bring the relevant price from column Q and place it in cell D16 for the month and year from the date format in cell F6. I have tried using a helper cell with

=TEXT(F6,"mmm")&"-"&TEXT(F6,"yy") to extract just the year and month but the vlookup still doesn’t work and I’m getting the #N/A result.

Any help would be greatly appreciated. Thanks

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### 6StringJazzer

##### Well-known Member
In the column Month, what is the actual data? The display is Mmm-yy but what shows up in the formula box when you select a cell?

Let's suppose it's an actual date that is the first day of each month formatted as "Mmm-yy" then you can use

=DATE(YEAR(F6),MONTH(F6),1)

#### etaf

##### Well-known Member
is the column P in TEXT or is it actually a date - but formatted to look like MMM-YY ?

IF TEXT then just =TEXT(F6,"mmm-yy") should work

Book1
DEFGHIJKLMNOPQ
1
2jan-21A
3feb-21B
4mar-21C
5
6A1/1/21
7
8
9
10
Sheet2
Cell Formulas
RangeFormula
D6D6=VLOOKUP(TEXT(F6,"MMM-YY"),P1:Q4,2,FALSE)

#### RoganLogan

##### New Member
What actually appears in the cell is 01/04/2021 and so on for each month. =DATE(YEAR(F6),MONTH(F6),1) works great. Thanks a lot both.

##### Well-known Member
Hi RoganLogan,

If the Month column is actual dates then this would work:

RoganLogan.xlsx
DEFGPQ
4MonthPrice
5Dec-20121
65/4/2021 11:00Jan-21133
7Feb-21106
8Mar-21132
9Apr-21112
10May-21120
11Jun-21108
12Jul-21104
13Aug-21121
14Sep-21125
15Oct-21104
16120Nov-21108
17Dec-21103
18Jan-22110
19Feb-22111
20Mar-22105
21Apr-22139
22May-22120
23Jun-22129
24Jul-22103
25Aug-22138
Sheet1
Cell Formulas
RangeFormula
D16D16=IFERROR(INDEX(\$Q\$5:\$Q\$25,AGGREGATE(15,6,ROW(\$P\$5:\$P\$25)-ROW(\$P\$4)/(TEXT(\$P\$5:\$P\$25,"mmm-yy")=TEXT(\$F\$6,"mmm-yy")),1)),"No match")

Replies
4
Views
219
Replies
3
Views
62
Replies
3
Views
58
Replies
13
Views
180
Replies
6
Views
97

1,130,193
Messages
5,640,771
Members
417,166
Latest member
Funwayo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back