Forum Discussion

Sob007's avatar
Sob007
Copper Contributor
Jun 09, 2022
Solved

Lookup value from multiple options

I am trying to look up the values for HOME, BUSN, CAMP emails in the respective columns highlighted in yellow (E,F,G)in the given screenshot, from columns C and D for the lookup value of column A.

 

The results under the columns in yellow are what I am looking for, if someone could help, please?

 

Example

excel
Formulas and Functions
    • Sob007's avatar
      Sob007
      Copper Contributor
      Jun 09, 2022

      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.

      • mathetes's avatar
        mathetes
        Silver Contributor
        Jun 09, 2022

        Sob007 

         

        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

        )

Resources