HLOOKUP with multiple criteria

[Rab_marr]

Good Afternoon all,

I have been searching the forum as well as other sites as to how I can resolve a little problem I have

I have a table in a cross tab format, something like this

(a) ---------------------(b) -----------------(c) ---------------(d) ---------------(e) -----------------(f) ------------(g)

1 white123
2
3 ------------------45678white123 --45679white123 --45679black456 --45680Jones124 --45680white123 --45681black456
4 ---------------------White123 --------white123 ---------black456 ---------Jones124 ---------white123 ------black456
5
6 Sam Smith
7 Ted Jones
8 Alan Black
9 Phil Green
10 Jeff White

What I am trying to achieve is run a lookup type formula that will search row 4 and find the last value that equals that in cell A1 and deliver the contents of the cell directly above it on row 3.

eg in the example above "white123" would skip the contents of column B & C and give the result in Col f = 45680white123

I hope I have made myself clear on what I am hoping to achieve, I am kind of confusing myself.

I thank you for your assistance in advance.

Rab

 

[63falcondude]

Try

=INDEX(B3:G3,1,MAX(IF(B4:G4=A1,COLUMN(B4:G4)-COLUMN(INDEX(B4:G4,1,1))+1))) Ctrl Shift Enter

Source: https://exceljet.net/formula/get-last-match

Another one that should work: =INDEX(B3:G3,1,SUMPRODUCT(MAX((B4:G4=A1)*(COLUMN(B4:G4))))-1)

 

[Rab_marr]

Thanks Falcondude,

I will give it a try and let you know how I got on.

Rab