Using Formulas to search multiple columns for one criteria & generate a list

[meghan1728]

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

[#2]

@meghan1728 

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.

[#3]

@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.

[#4]

@meghan1728 

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.

[#5 · Accepted solution]

@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.

[#6]

@mtarler 

Hoist on my own petard. Here that errant sheet is.

[#7]

@mtarler 

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. 

[#8]

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.

[#9]

With which I completely agree. My point was that the user, once understanding how it all works together, has "a nicer looking" workbook when "background stuff" is in the background, "foreground" in the foreground. And wanted the user to know that's possible.

[#10]

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.

[#11]

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.

[#12]

@mtarler 

This is what I fiered. I've a company managed computer, sa I can't force updates... This is why I provided the version# of my xlsx. Is it supposed to be ok ? What is the version@ of your xlsx ?

[#13]

I have the 'Current Channel' and have version 2301. Here is a link to what version each of the channels are presently released: https://learn.microsoft.com/en-us/officeupdates/update-history-microsoft365-apps-by-date
at version 2108 you may be "up to date" if you have a Semi-Annual Enterprise Channel.
you can also see the Features Flyer (aka.ms/ExcelFeaturesFlyer) to see features by version.

[#14]

thank you for your feedback.
It should work by me as I do have a have a Semi annual Enterprise Channel ...
P.S. I was talking of your first example