CODEWITHSUNDEEP
excelexcel-formula
griegs
griegs

Reputation: 22760

Lookup which list a name is in and return the top value

In my sheet, I have 3 named lists. I want to lookup which list Person4 is in and return the "List2" title.

So far I have this; =MATCH(TRUE,List4=E39,0) which is fine and it gives me back the index value of the name. But I would now need to somehow return List1, List2 or List3.

But further than that, I do not know which list that person will be in. So ideally I need a formula where I can grab the name of the person I want to look up, have it then scan all three lists, and return the title of the list the person appears in.

I guess I could use VLookup across all lists for a particular match, but how do I then get the title of the list for that match?

enter image description here

Upvotes: 1

Views: 753

Answers (2)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

In case you don't have Excel365 available, you can do it with SUMPRODUCT and INDEX:

enter image description here

My formula in B10 is:

=INDEX(A1:E1;1;SUMPRODUCT(--(A2:E5=B9)*COLUMN(A2:E5)))

Upvotes: 2

JMP
JMP

Reputation: 4467

You could use an XMATCH for each list, and an IFS statement to go through the list. Note that IFS returns the first correct match, and then terminates.

=IFS(
NOT(ISNA(XMATCH(A8,A2:A4))),A1,
NOT(ISNA(XMATCH(A8,B2:B3))),B1,
NOT(ISNA(XMATCH(A8,C2:C5))),C1)

multi xmatch

A8 is the item to lookup.

Upvotes: 4

Related Questions