Search multiple list for a value and return the name of the list

[PJVV77]

Hi,

I had to set up twelve separate sheets to group FTEs by function - monthly I then use a simple INDEX MATCH formula to return their FTE count for that month from a master data file.

As I get my new monthly master file, people are added or removed and what I am trying to do is a formula (in one column) that can look up whether an ID existed in the previous month's function file and return the name of that function; that way I can quickly work out who has been mapped and where, anyone that does not appear in one of my twelve lists are therefore new additions.

I have previously done this in 12 columns for each list using the match function. I want to see whether this can be done showing the result in just one column (without still running the 12 columns and then finding another formula to try and identify which function the ID is in). Seeing that you cannot nest more than 7 ifs, I need to find another way. I tried the IFS function, but it does not work as it returns the #N/A value if it does not find the ID in any of the lists.

Ideally I would be able to run through list one with say MATCH and the return "Finance" if the ID exists there, if not it will check list 2 and if the ID exists there return "Development", if not, go to the next list etc. etc.

Any thoughts?

Thanks,
Pieter

 

[Jeffrey Mahoney]

Can you merge all your data? I guess the question really is, why do the tables have to be separate?

Create a new field that includes the department descriptors "Finance" and "Development", and so on. Also include the date so you can filter by month. Merge all your data sets. Now you can use Index /Match, or any other lookup function to return your FTE and the department.

I am a huge proponent to having a single table instead of multiple sheets to manage data. Formulas don't have to be changed each time you add another month sheet.

 

[PJVV77]

Can you merge all your data? I guess the question really is, why do the tables have to be separate?

Create a new field that includes the department descriptors "Finance" and "Development", and so on. Also include the date so you can filter by month. Merge all your data sets. Now you can use Index /Match, or any other lookup function to return your FTE and the department.

I am a huge proponent to having a single table instead of multiple sheets to manage data. Formulas don't have to be changed each time you add another month sheet.

Thanks for the note!

If I could I would have it merged, unfortunately given confidentiality of information and format requests there is little option but to split them. I will continue with the old method and continue to search for that elusive formula to make it a more efficient process.