Return text if line item matches any line item in a column on another worksheet

[Churchy LaFemme]

Not sure how to phrase this - and I am nearly positive I've done it before and forgot how.

Several tabs in worksheet. One tab, the master tab, lists people the known population of Storyville.

Other tabs have the populations magical kingdoms.

I have columns on the master tab for each of the magical kingdoms (Snow White's Kingdom, King Midas' Kingdom, Sleepy Beauty's Kingdom, etc.) and I need a formula that will return the word "resident" if a person* on the master tab is found on that kingdom tab.

Storyville Display	Alias	Snow White's Kingdom	Wonderland
Cora	Barbara_seagull@hotmail.com	Resident	Resident
Pongo	Ohmidog@hotmail.com

<TBODY>
</TBODY>

The above would be correct because Pongo does not appear on any tabs and Cora appears on the tab for Snow White's Kingdom and for Wonderland.

What I am missing? I can't make this work with Match, Index-Match, or various if and search strategies.

This is in the neighborhood, but not exactly what I want.
http://www.mrexcel.com/forum/excel-questions/546750-if-cell-contains-text-found-list.html

*Because I am crafty, people have an ID column that uses email alias rather than display name so that on all tabs, robert_carlyle@hotmail.com will be used as the ID for Rumpelstiltskin, Mr. Gold or any other name he uses.

 

[CodeNinja]

If your sheets are named "Snow White's Kingdom" and "Wonderland" then you can use the indirect function to reference those sheets:

Assuming the table you have above starts at A1 (Storyville Display) etc... and the names of the storybook characters are on the sheet in column A, rows 1-12:

=IF(ISNA(MATCH($A2,INDIRECT("'"&C$1&"'!$A$1:$A$12"),0)),"","Resident")

I hope this makes sense....

 

[Aladin Akyurek]

C2, copy across and down:

=IFERROR(IF(VLOOKUP($A2,INDIRECT(C$1),1,0)=$A2,"Resident"),"")

where C1 is a string INDIRECT can interpret as a table that resides on a relevant sheet.

 

[Churchy LaFemme]

This is giving me all "Resident" results. Can you explain what "&C$1&" refers to?

On my sample table, that would be a cell with the formula (in the column for Snow White's Kingdom, and would be circular.) I replaced it with ... with nothing, actually.

 

[Churchy LaFemme]

Sorry, it was CodeNinja that I replied to.

CodeNinja's formula give me all "resident" results.
Aladin Akyurek's formula gives me no "resident" results (Possibly because the default sort on the tab is unfriendly to a lookup function.)

One thing I'm not understanding is why INDIRECT is the go-to here. I wouldn't have thought of that one.

 

[Aladin Akyurek]

Sorry, it was CodeNinja that I replied to.

CodeNinja's formula give me all "resident" results.
Aladin Akyurek's formula gives me no "resident" results (Possibly because the default sort on the tab is unfriendly to a lookup function.)

One thing I'm not understanding is why INDIRECT is the go-to here. I wouldn't have thought of that one.

Could you post a 5-rows sample from Snow White's Kingdom?

 

[CodeNinja]

Sorry, it was CodeNinja that I replied to.

CodeNinja's formula give me all "resident" results.
Aladin Akyurek's formula gives me no "resident" results (Possibly because the default sort on the tab is unfriendly to a lookup function.)

One thing I'm not understanding is why INDIRECT is the go-to here. I wouldn't have thought of that one.

Any sheet that has a space in the name needs a single quote around it, so that is why I put the single quote. There is a problem with snow white's kingdom because the string snow white's kingdom contains a single quote. My formula works perfectly if you remove that single quote. I am not sure how to overcome that issue as substituting the ' with ~' does not seem to resolve it either. Perhaps a little more study will provide a solution. Regardless of whether you use vlookup or match, you are still going to need the indirect statement.

My suggestion is to remove the ' from the sheet and reference cell containing "white's" and the formula should work fine.

Sorry to confuse.

 

[Churchy LaFemme]

Here is the master tab showing the expected results for Snow White's Kindgom

Storyville	Alias	Snow White's Kingdom	Wonderland
Archie Hopper	Raphael_Sbarge@hotmail.com	Resident
Cora	Barbara_seagull@hotmail.com	Resident	Resident
Emma	Jennifer_Morrison@hotmail.com
Mary Margaret	Ginnifer_Goodwin@hotmail.com	Resident
Mr. Gold	robert_carlyle@hotmail.com	Resident
Pongo	Ohmidog@hotmail.com
Ruby	Meghan_Ory@hotmail.com	Resident

<TBODY>
</TBODY>

And here is the Snow White's Kingdom tab that would produce the "Resident" results.

Snow White	Alias
Cora	Barbara_seagull@hotmail.com
Jiminy Cricket	Raphael_Sbarge@hotmail.com
Red Riding Hood	Meghan_Ory@hotmail.com
Rumpelstiltskin	robert_carlyle@hotmail.com
Snow	Ginnifer_Goodwin@hotmail.com

<TBODY>
</TBODY>

Thank you for your help on this. I really think I have done this before and am having Excel amnesia.

 

[CodeNinja]

What are you naming your sheets? That is critical here...

 

[Churchy LaFemme]

Storyville, Snow_White, King_Midas, like that. I keep sheet names simple and don't use spaces because I spend time in Access and SharePoint.