Forum Discussion
Lookup value from multiple options
If you have a recent version of Excel, the FILTER function would work well. I've attached an example.
mathetes Thank you so much, it worked great! Would you be also able to suggest if it could work while looking up the table from a different workbook? I tried imitating the formula from a different workbook, but I believe I am messing it up somehow and it shows that the formula is incorrect.
FILTER most definitely can work pulling data from (and filtering it) a different table. So I don't know what you're doing but maybe you should try building it from scratch rather than trying to imitate. I create mine always by starting in the destination spot with =FILTER( but then going to the source and highlighting the various ranges as needed). That is, I don't just "write the formula"
You also need to know that FILTER is what's called a dynamic array function, so it will return multiple rows of data if more than one row meet the criteria established. That wasn't the case with your example, but if John Doe had had two home addresses, it would have..
Here are two good sources that might help.
https://exceljet.net/excel-functions/excel-filter-function
https://www.youtube.com/watch?v=9I9DtFOVPIg
If you still want help come back with some more complete descriptions. (starting a new thread, ideally
)