SOLVED

Lookup value from multiple options

%3CLINGO-SUB%20id%3D%22lingo-sub-3488146%22%20slang%3D%22en-US%22%3ELookup%20value%20from%20multiple%20options%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3488146%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20look%20up%20the%20values%20for%20HOME%2C%20BUSN%2C%20CAMP%20emails%20in%20the%20respective%20columns%20highlighted%20in%20yellow%20(E%2CF%2CG)in%20the%20given%20screenshot%2C%20from%20columns%20C%20and%20D%20for%20the%20lookup%20value%20of%20column%20A.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20results%20under%20the%20columns%20in%20yellow%20are%20what%20I%20am%20looking%20for%2C%20if%20someone%20could%20help%2C%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Example%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Example%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Example%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Example%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Example%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Example%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Example%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Example%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Example%22%20style%3D%22width%3A%20999px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Example%22%20style%3D%22width%3A%20999px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F378973i3D717862C8BC7DE5%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Lookup%20from%20multiple%20options.jpg%22%20alt%3D%22Example%22%20%2F%3E%3Cspan%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EExample%3C%2Fspan%3E%3C%2Fspan%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EExample%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EExample%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EExample%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EExample%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EExample%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EExample%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EExample%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EExample%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EExample%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3488146%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3488287%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20value%20from%20multiple%20options%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3488287%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1419130%22%20target%3D%22_blank%22%3E%40Sob007%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20a%20recent%20version%20of%20Excel%2C%20the%20FILTER%20function%20would%20work%20well.%20I've%20attached%20an%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3489126%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20value%20from%20multiple%20options%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3489126%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3BThank%20you%20so%20much%2C%20it%20worked%20great!%20Would%20you%20be%20also%20able%20to%20suggest%20if%20it%20could%20work%20while%20looking%20up%20the%20table%20from%20a%20different%20workbook%3F%20I%20tried%20imitating%20the%20formula%20from%20a%20different%20workbook%2C%20but%20I%20believe%20I%20am%20messing%20it%20up%20somehow%20and%20it%20shows%20that%20the%20formula%20is%20incorrect.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3489691%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20value%20from%20multiple%20options%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3489691%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1419130%22%20target%3D%22_blank%22%3E%40Sob007%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFILTER%20most%20definitely%20can%20work%20pulling%20data%20from%20(and%20filtering%20it)%20a%20different%20table.%20So%20I%20don't%20know%20what%20you're%20doing%20but%20maybe%20you%20should%20try%20building%20it%20from%20scratch%20rather%20than%20trying%20to%20imitate.%20I%20create%20mine%20always%20by%20starting%20in%20the%20destination%20spot%20with%20%3DFILTER(%20but%20then%20going%20to%20the%20source%20and%20highlighting%20the%20various%20ranges%20as%20needed).%20That%20is%2C%20I%20don't%20just%20%22write%20the%20formula%22%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20also%20need%20to%20know%20that%20FILTER%20is%20what's%20called%20a%20dynamic%20array%20function%2C%20so%20it%20will%20return%20multiple%20rows%20of%20data%20if%20more%20than%20one%20row%20meet%20the%20criteria%20established.%20That%20wasn't%20the%20case%20with%20your%20example%2C%20but%20if%20John%20Doe%20had%20had%20two%20home%20addresses%2C%20it%20would%20have..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20are%20two%20good%20sources%20that%20might%20help.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-filter-function%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-filter-function%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20still%20want%20help%20come%20back%20with%20some%20more%20complete%20descriptions.%20(starting%20a%20new%20thread%2C%20ideally%3C%2FP%3E%3CP%3E)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3489902%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20value%20from%20multiple%20options%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3489902%22%20slang%3D%22en-US%22%3ESure%2C%20will%20go%20through%20the%20sources%20and%20create%20a%20new%20thread%20if%20I%20still%20need%20more%20help.%3CBR%20%2F%3EThanks%20again%20for%20your%20time%20on%20this.%20It%20has%20been%20very%20helpful!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3489921%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20value%20from%20multiple%20options%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3489921%22%20slang%3D%22en-US%22%3EYou're%20very%20welcome%3C%2FLINGO-BODY%3E
New Contributor

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?

 

ExampleExample

5 Replies
best response confirmed by Sob007 (New Contributor)
Solution

@Sob007 

 

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.

@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

)

Sure, will go through the sources and create a new thread if I still need more help.
Thanks again for your time on this. It has been very helpful!
You're very welcome