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

PJVV77

New Member
Joined
Aug 2, 2019
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 2, 2019
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,785
Messages
5,655,290
Members
418,186
Latest member
mrahim

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top