Forum Discussion
Solved
Using Formulas to search multiple columns for one criteria & generate a list
Hey all! I hope I'm able to type this in a way where it makes sense. For my job, I'm trying to fix up our list of interpreters to make it easier to navigate. There are currently three sheets: the...
mathetes hey John, not to give you a hard time but I guess you are so lazy you forgot to attach your workbook. LOL. Sry I couldn't resist the poke. But seriously, I absolutely agree with you that another format would be much more efficient so updated my sheet with a recommended format. I'm curious how closely it will match your sheet.
meghan1728 attached is an example showing a sample 'data' set, pulling a UNIQUE set of all languages, Data validation for drop-down, and output table of matching results. I formatted the data as a table to make it easy to expand and contract as needed.
I've modified and built on the excellent start that mtarler provided (I'm clearly lazier than he, since he put the effort in to actually create something!)..... just to show a different way to organize the table of Name and Language, resulting in slightly different, simpler UNIQUE and FILTER formulas to yield the same result--a list of names of interpreters who have the language skill sought.
For both of these solutions, please note that you will need Excel 2021 or newer, in order to use the UNIQUE and FILTER functions.
mathetes hey John, not to give you a hard time but I guess you are so lazy you forgot to attach your workbook. LOL. Sry I couldn't resist the poke. But seriously, I absolutely agree with you that another format would be much more efficient so updated my sheet with a recommended format. I'm curious how closely it will match your sheet.
You took it all to the next level. Great job!
The only "improvement" left would be to put the basic tables in the background (on a separate sheet) and have a "front page" where one selects the language desired and gets the resulting list.
- I completely agree but kept them on the same sheet to show the 2 different approaches and leave it to the user to move the components to their own sheets. I also feel when the components are on different sheets it makes it harder for the user to see how they all work together.
