Index Match Formula doesn't recognize output from Right Formula as lookup value.

The values in cells D1 and D2 are likely numbers, but the output of your RIGHT function will be treated as text. Try this approach:

Please Login or Register  to view this content.

The problem is that the value in A1 is a date and the RIGHT function is returning the last 2 digits from the serial number for the date.

Change the format of A1 to text then re-enter Jan-12 this will make the entry of Jan-12 text that the RIGHT function will recognize the way that you want.

I don't recommend changing valid dates to a text as it ruins them for further calculations that require true dates/numbers.

=INDEX(E1:E2,MATCH(RIGHT(YEAR(A1),2)+0,D1:D2,0))

I don't disagree with Palmetto. If the date is just an identifier, it doesn't matter, but if you are going to be doing calculations with the date, it would be easier to have the date a "real" date and use a formula such as he gave you to make the calculation requested.

I know this will get you the last two digits for the year. you can incorporate it in your formula.

=TEXT(K4,"YY")

I identify somewhat with the O.P. in this regard: when I paste Access grids into Excel, and there is a "potentially" numeric value but to Access it's text (e.g. 0000777), it looks like 0000777 when you paste it...

Try
SELECT "0000777" AS Expr1, "0123" AS Expr2
FROM anytable;
(Pick a table with few records for fast results).

When you paste the result to Excel, and hit F2 and enter on a pasted cell, it changes similar to OP's description. Excel "feature not a bug" stuffs what appears to be a numeric conversion down your throat. Worse yet, if you wanted to edit 0000777 and change it to 0000779, you better not use F2 (or click in the formula bar, like OP) and enter, or those leading digits are gone, forever. BTW, if you search and replace 7 with 9, the same thing happens.

I don't know how to turn off this behavior. I've looked for an option. (But, hey, who am I, a user(cough, sheep, cough), to presume that I know what I want better than Excel developers who hardcode what they know is best for me). Related: is there a way to "turn off" Text-To-Column "memory?" It's a great feature, but after you do it once, it keeps doing it on future pastes, whether you want to or not. A feature, you know.

I'm not sure that this exactly equates to OP dilemma. Dates are numbers though, and in my example things "shift to the right" like OP described.

Loiselle, that is a great discovery. I very much like your thinking about accomplishing this without having to succumb to the idiotic need to close and reopen Excel!