Forum Discussion
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 first sheet is the main sheet where the search results would appear, the second sheet is just a list of the different languages people can search for, and the third sheet is all of the information of the interpreters. On the first sheet, I have a cell with a drop down menu filled with the languages from the second sheet, and using that menu people can search based on which language they need.
My issue is this: a lot of these interpreters speak multiple languages, so if someone searches for one particular language, an interpreter can show up on multiple lists. Currently, I have the third sheet set up as a table, with the name, phone number, email, and other notes as separate columns. To the side of this table I have every language each interpreter speaks, with one language in each column (so some interpreters have 5 or 6 columns after their row in the table).
How should I go about writing a formula so someone can select a language from that menu, and the interpreter's information will appear? I have columns set up on the first sheet for the information to generate into, but I just can't figure out how to get there. Should I put all of the languages in one column instead of multiple and separate them by commas, or are different columns okay? I can try to get a dummy list going if that will help everyone visualize
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.
- Hello,
This example is exactly what I'm looking for : I receive a file with in the first column the name (of a server) and in the next columnS the various IP this server have (the # of IPs may vary from one server name to the next one).
It is nt possible for me to regenerate the file like you did in your next exemples).
BUT
when I reselect language Dutch in cell J1, it comes with an error (#NAMe?) in cell L2 where I should see the list of persons.
It looks like you uses a library I do not have ???
Thank you for your help. (I have MS Office 365, xlsx v2108)
P.S. : sorry for my bad English, I Hope my question is clear.- with MS office 365 you should have access to those functions. Make sure you have updated it. Some have their update set to manual or blocked so make sure you update.
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.
If I were doing this, I'd use one column for all languages and repeat the names and contact info of the interpreters on each row. That's because it's languages that are at the heart of what you're doing. Pick a language and I'll find you an interpreter.
If you were working in Access (a relational database) rather than Excel, it would make sense to have all the information on the person just once, with an ID, and repeat the IDs next to each language. But presumably your lists (of both languages and interpreters) are finite. This could be done in Excel too, but unless we're dealing with a really long list of names and languages, it might be overkill.
The other thing is to avail yourself of the UNIQUE and FILTER functions to deliver the results you're seeking. Here's a YouTube video, if you're not familiar with them. https://www.youtube.com/watch?v=9I9DtFOVPIg
And by all means, feel free to post a dummy copy of the sheets you're creating. I and others would be happy to show you how FILTER could deliver.
