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

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
Joined
Jan 27, 2010
Messages
846
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 24, 2012
Messages
4,683
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Dec 7, 2011
Messages
45
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.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,466
Office Version
  1. 2016
Platform
  1. Windows
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")
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top