Vlookup Month and Year

RoganLogan

New Member
Joined
Dec 7, 2011
Messages
45
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.

1617374537160.png

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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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)
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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")
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top