Forum Discussion
Using Formulas to search multiple columns for one criteria & generate a list
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.