FlexYourData has a good point about it being a bad idea to have your date in your table broken into 2 columns. ONE of the reason is that in order for VLOOKUP to work, it indexes based on the first column of the table only, not the first two.

**VLOOKUP as the data is presented currently will not work, but there are 3 potential options listed below:**

### 1) Helper Column

Place a helper column either to the left or right of the date columns in your reference table. In the helper column concatenate the Month and the Day. This column can be hidden for presentation purposes. When performing your VLOOKUP concatenate your to reference dates, and your reference look up table starts with your Helper column

```
Helper column to the right of dates
=VLOOKUP(MONTH&DAY,TABLE,MATCH(INTIALS,INITIALS_RANGE,0)+1,0)
or
Helper column to the left of dates
=VLOOKUP(MONTH&DAY,TABLE,MATCH(INTIALS,INITIALS_RANGE,0)+3,0)
```

### 2) Replace the dates

In your reference table you can replace your dates with either a single date column as suggested by FlexYourData or leave it as two date columns that each contain an excel date. I will describe the later in more detail. The problem here is that you will lose the ST,ND,RD,TH on the day in the reference table. IF this is important than this method will not work

In your column enter 20/01/01 (whatever matches your date format for the first day of the year) in both the MONTH cell and the DAY cell, or enter it in one and make the other equal the first with a formula.

Then custom format the cells. Use the formatting of MMM for month and D for day
Now since the full date is actually contained in each cell, you only need to reference day column when defining your lookup table.

the next hurdle will be converting your lookup date into a date. This can either be achieved by entering a full date instead of separate values, or you can combine the separate values to make a date. To make a date out of separate values you can use the DATE function as follows:

```
=DATE(YEAR,MONTH,DAY)
```

So since you do not have a year, you can manually set that to match the year you used when your set up your reference table, or you can add it as an additional cell as part of the look up, or your can pull if from the first date cell in your table. For the later case, if you first table date was in cell L4 you could do the following:

```
=YEAR(L4)
```

To get the month (Assuming in D4) you can opt for the following formula to convert from text to a number:

```
=MATCH($D$4,{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0)
```

To get the day as a number the preferred method is to have it as a number with no "st", "nd", "rd", or "th" after it. how ever if you do have to strip the number out then you could use the following formula with your reference days assumed to be in D5.

```
=LEFT($D$5,len($D$5)-2)
```

Now that you have all three values, insert them into the date formula and you wind up with something that looks like the following:

```
=DATE(YEAR($L$4),MATCH($D$4,{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0),LEFT($D$5,len($D$5)-2))
or
Hard Coded Year
=DATE(2020,MATCH($D$4,{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0),LEFT($D$5,len($D$5)-2))
```

So assuming your reference table is L3:R34 your vlookup would become:

```
=VLOOKUP(DATE(2020,MATCH($D$4,{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0),LEFT($D$5,len($D$5)-2)),$N$3:$R$34,MATCH(INTIALS,INITIALS_RANGE,0)+1),0)
```

Note the look up range is actually N4:R34 since you are ignoring the month column.

### 3) USE Alternate Formula

Someone else mentioned used COUNTIFS. That is one option and works great for numbers. Not so great if for some reason your needed to return text value. The combination of INDEX and MATCH will work for you.

MATCH will be used twice. once to determine the appropriate row and once to determine the appropriate column (eluded to in options above). MATCH will return the position of what your a looking for in a range/list. Tell it what you want to search for, what the range/list to look in is and what type of match you want to make.

so to find your initials in the assumed the reference table in L3:R34 with row 3 being the initials and the initials you want to look up are in A10 then your match formula would be:

```
=MATCH(A10,$N$3:$R$3,0)
```

Notice 3 things. The first is I locked the range reference to avoid having the cells change as I copy the formula. The second is I started the range at the first initial instead of the edge of the table. The third is that the result is the position if the range and NOT the column of your spreadsheet NOR the column of your reference table.

For VLOOKUP you need to align the result to give the column of your table. You will need to manually add the number of extra columns before the initials. In your case it is 2, so the formula becomes:

```
=MATCH(A10,$N$3:$R$3,0)+2
```

Now to match the row it is a bit trickier and uses an array formula. Concatenate the Month (D4) and Day (D5) you are looking for and for the look up range, Concatenate the two ranges. You formula becomes

```
=MATCH($D$4&$D$5,$L$4:$L$34&$M$4:$M$34,0)
```

now the above is an array formula so you will need to use `CONTROL`+`SHIFT`+`ENTER` instead of just `ENTER` when confirming your formulas. You will know you have done it right when { } show up around your formula in the formula bar. note that they cannot be added manually.

The the last part is using index to define the table you want to look in. Unlike VLOOKUP, you only want to include the reference data and not the header rows and columns. This also means that the results from the MATCH formulas will align with the data without ajustments. So with the assumed reference table in L3:R34 your index function will look like this:

```
=INDEX($N$4:$R$34, ROW TO LOOK IN, COLUMN TO LOOK IN)
```

when you substitute you match formulas in it becomes:

```
=INDEX($N$4:$R$34,MATCH($D$4&$D$5,$L$4:$L$34&$M$4:$M$34,0),MATCH(A10,$N$3:$R$3,0))
```

REMEMBER it is an array formula so need the special CSE formula confrimation instead of just Enter.