Index/Match Help

[abelperr]

Hi all,

Relatively new to excel but I think this should be pretty easy to do but I cant get it to work right. heres a sample of that im working with:

A B
1 X Y
2 G H
3 F K
4 Z Y
5 P Y

Basically, im working with an expense report and want to get information to the left of B so I cant use vlookup. Index/match will work to a point. Without sorting, which would defeat the purpose of what I am trying to accomplish, I can only get it to work correctly for some of them. With =Index(A1:A5,MATCH(Y,B1:B5)), if i try and apply it to multiple cells (using ctrl enter), the first and second Y's work, but since the reference on the index only goes up by increments of 1 (A1 to A2 to A3 etc for each consecutive cell), it is starting the index search at A3 for the 3rd value and thus returning Z instead of P from column A. Ideally it would skip the "Ax" value to the one after the previous match. Any easy ways to fix this???

Thanks in advance

[jeffreybrown]

I'm thinking

Please Login or Register  to view this content.

[abelperr]

Whoops, I forgot to include the 0 in my original post. Thats the formula im currently using where I run in to the issue of the second case being repeated instead of the 3rd

[jeffreybrown]

Here are three possible solutions to answer what I believe you are asking

Sheet1 >> code
Sheet2 >> pivot table
Sheet3 >> CSE formula

[martindwilson]

and here's a fourth using a helper