Change cell contents if found in a list on another sheet

First.... add the missing sheets.

Second, use this formula:

=IF(ISNUMBER(MATCH(E2, Early!$E:$E, 0)), "EARLY MEMBER", IF(ISNUMBER(MATCH(E2, Late!$E:$E, 0)), "LATE MEMBER", IF(ISNUMBER(MATCH(E2, Night!$E:$E, 0)), "NIGHT MEMBER", "UNKNOWN")))

You would probably find it a lot easier if you combined Early, Late and Night into one sheet. Just add a column to differentiate them as early, late or night ... and that could be a Data Validation List for consistent entry.

You can sort and filter this list if you want to view each category and analyse the data with a Pivot Table. You can't do any of that in separate sheets.

Regards, TMS

Would you be able to give me an example of what you mean using the eample I attached?

@fastcar: sorry, didn't seem to be much point when you had a solution already. More to the point, a solution matching your existing structure.

If you do still want it, I'll put something together.

I think TMS is simply underlining that keeping 3 separate "data sheets" is an inherent pain, and atypical if you need to create reports from data that comes from all 3 sets of data.

More typical is a SINGLE database where all rows are common. One of the columns in this consolidated database would be "MEMBERSHIP" and in that column you enter the group to which they attend. LATE, EARLY, NIGHT.

You can use Data Validation > List on that column to put a drop down in those cells to make it easy to select LATE/EARLY/NIGHT since those are the only acceptable entries.

Now that you have all the data on a single sheet, it is MUCH simpler to get reports from that data. And you can turn on the Data > Filter to get drop downs across the top of the data that allows you to do real-time filtering. You could filter the data in the newly added column for LATE and bingo... you're looking at only LATE membership. Edit the data, print the filtered list, whatever you need, then filter for EARLY, or NIGHT, or remove the filter to see all data again.

A single database is a far superior database.

To be honest, JB has offered an absolutely superb and accurate summary of the approach I was outlining.

I would just use VLOOKUP or INDEX/MATCH to return the Membership status.

Combine all the existing sheets into one sheet, say Status. In column F, add the Membership status..

The formula would look like:

=IF(ISERROR(VLOOKUP(E2,Status!E:F,2,FALSE)),"Unknown",VLOOKUP(E2,Status!E:F,2,FALSE))

But, that said, given that the data on the two sheets would be virtually identical, there wouldn't actually be much point having a separate database sheet.

On that basis, we go from four sheets, down to two sheets, down to one sheet.

But, with that one sheet, you can create a Table, you can sort, you can filter, you can analyse with Pivot Tables and Charts ... if you wish.

Regards, TMS

You're welcome. Thanks for the rep.