Vlookup/Index based on a person name and date

[jimbogarner]

Hi,

Apologies as I am an excel novice so this may be an easy fix...

I am trying to pull together a costing file whereby if someone enters the following in a sheet, it would return a cost for that person working on that date:

Cell A1: 03-May-2019
Cell B1: Peter Pan
Cell C1: Cost to return from Sheet 2

The issue I am having, is I am trying to get this to lookup from a grid on another sheet (Column $A:$A is a list of names, Row $1:$1 is Cell B1: Jan-19, Cell C1: Feb-19, Cell D1: Mar-19 etc.), however, I can't get it to ignore the day in the formula (in above example 3rd).

I assume the index function would work best but I still can't this to work (nor fully understand it!)

Any help is appreciated.

Thanks,

James

 

[steve the fish]

Click cell B1 in the lookup table. What is displayed in the formula bar?

 

[jimbogarner]

01/05/2019

I can't change it to 03/05/2019 because I will have people working on the 2nd May, 5th May etc. etc. but they will charge me a set 'May' fee.

Thanks,

James

 

[Eric W]

I assume that the value in A1 is an actual Excel numerical date, formatted to display as a date. What you need to do depends on how the values are saved on your other sheet. If the dates are the other sheet are text values as shown, then use this

TEXT(A1,"mmm-yy")

as your lookup value, not A1. If the dates on the other sheet are actual dates, possibly the 1st day of each month, and formatted to show just the Month-YY, then use this

EOMONTH(A1,-1)+1

as your lookup value.

 

[steve the fish]

Try an adaptation of this:

=INDEX(Sheet2!A1:Z1000,MATCH(A2,Sheet2!A1:A1000,0),MATCH(A1-DAY(A1)+1,Sheet2!A1:Z1,0))

 

[jimbogarner]

Worked a charm - thanks very much

James