Lookup on variable entries

[jasonbridge]

Hi,

I am trying to get a value based on the following:

I have a table that has 5 columns each with a different heading (system type), then after these comes another 3 columns that gives user details. In the first 5 columns there is a user ID in each cell e.g.

LRSMS MRSMS RRSMS GRSMS WRSMS Name Manager Location
12345 56789 54878 12989 68595 John Sally London
56789 59800 12989 55555 68790 Tim Sally London

Then I have another worksheet that has one column where the cell has one of the 5 SMS codes and then in the next column has the user ID. What I need to do is do a sort of lookup that first recognises the system type and then looks up in the corresponding column for the matching user ID. e.g. If the system is LRSMS and the ID is 56789 then it returns Tim as the user.

Hope this makes sense, can anyone help?

Cheers
Jason

 

[Andrew Poulsom]

Try:

=INDEX(Sheet1!F1:F3,MATCH(A2,INDEX(Sheet1!A1:E3,0,MATCH(A1,Sheet1!A1:E1,FALSE)),FALSE))

where A1 contains LRSMS and A2 contains 56789.

 

[jasonbridge]

Bingo! Thanks for that it is greatly appreciated