Lookup on variable entries

jasonbridge

New Member
Joined
Mar 8, 2005
Messages
8
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,204
Messages
5,527,401
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top