Finding a match when there are multiple items in a cell

[martinc]

Hello,


Is it possible to perform a vlookup, or some other lookup function, against a column when the cells in that column contain multiple values?


For example, I have a STAFF ID table with two columns. Column A contains a list of staff IDs. Some cells here have a single Staff ID but other cells may contain two or more staff IDS separated by a single space.


e.g cell A1=SN1000

Cell A2= SN1001 SN1002


In column B I need to insert the staff name which I hope to do by matching against a STAFF worksheet. The STAFF worksheet lists all staff numbers and staff names in separate rows in two columns.


I hope this makes sense. If you need me to clarify let me know.


Many thanks,


Martin

 

[dan_l]

Depends on how predictable the staff id's are.


Try putting your vlookup in a left or right formula.


IE where named range of source data is test:


test1 sn1 sn2

test1 sn3 sn4


=LEFT(VLOOKUP("test1",test,2,FALSE),3)

Returns "sn1". Pairing it with a =right( instead of a =left( to return a second value or N/A which you could wash with a iserror(

 

[dan_l]

I shouldn't even make that first comment. There are ways to negate unpredictable length in the staff id number, it's just that they're a bit more complex then what I shared above.

 

[Hui]

Martinc

When you do a lookup you will need to do something like

=+INDEX(A1:B10,MATCH("*"&"SN1002"&"*",A1:A10,0),2)

or

=+INDEX(A1:B10,MATCH("*"&D10&"*",A1:A10,0),2)


That will lookup SN1002 or the valuie in D10 in the Range A1:A10 and then return the value in Column B next to your value


You can change "SN1002" to say D10 etc to have an entry cell somewhere else

 

[martinc]

Thanks Hui and Dan_l for taking the time to post a suggestion to this query. I'll try these out and see how I get on.