SOLVED

I need a lookup function to find a result in column A and find the answer in the colums b to E

%3CLINGO-SUB%20id%3D%22lingo-sub-2806575%22%20slang%3D%22en-US%22%3EI%20need%20a%20lookup%20function%20to%20find%20a%20result%20in%20column%20A%20and%20find%20the%20answer%20in%20the%20colums%20b%20to%20E%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2806575%22%20slang%3D%22en-US%22%3E%3CTABLE%20border%3D%220%22%20width%3D%22320%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2219%22%3EChart%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EMAX%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EMAX-N%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EDiscover%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EReveal%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EA2%3C%2FTD%3E%3CTD%3EMAX%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EA3%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EMAX-N%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EA4%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EDiscover%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EA5%3C%2FTD%3E%3CTD%3EMAX%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EA6%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EMAX-N%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EA7%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EDiscover%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EA8%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EReveal%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3EA9%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EReveal%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2806575%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2806600%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20a%20lookup%20function%20to%20find%20a%20result%20in%20column%20A%20and%20find%20the%20answer%20in%20the%20colums%20b%20to%20E%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2806600%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1173392%22%20target%3D%22_blank%22%3E%40Andy_Laurence%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20want%20to%20return%20the%20single%20non-blank%20value%2C%20or%20all%20four%20values%20(blank%20and%20non-blank)%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2806759%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20a%20lookup%20function%20to%20find%20a%20result%20in%20column%20A%20and%20find%20the%20answer%20in%20the%20colums%20b%20to%20E%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2806759%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3EThanks.%20The%20single%20non-blank%20value.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2806853%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20a%20lookup%20function%20to%20find%20a%20result%20in%20column%20A%20and%20find%20the%20answer%20in%20the%20colums%20b%20to%20E%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2806853%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1173392%22%20target%3D%22_blank%22%3E%40Andy_Laurence%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20range%20is%20in%20A2%3AE9%20and%20the%20value%20you%20want%20to%20look%20up%20is%20in%20H2.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLOOKUP(REPT(%22z%22%2C255)%2CINDEX(%24B%242%3A%24E%249%2CMATCH(H2%2C%24A%242%3A%24A%249%2C0)%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2806925%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20a%20lookup%20function%20to%20find%20a%20result%20in%20column%20A%20and%20find%20the%20answer%20in%20the%20colums%20b%20to%20E%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2806925%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1173392%22%20target%3D%22_blank%22%3E%40Andy_Laurence%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20an%20alternative%20-%20using%20the%20same%20setup%20as%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DCONCAT(FILTER(%24B%242%3A%24E%249%2C%24A%242%3A%24A%249%3DH2))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2833032%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20a%20lookup%20function%20to%20find%20a%20result%20in%20column%20A%20and%20find%20the%20answer%20in%20the%20colums%20b%20to%20E%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2833032%22%20slang%3D%22en-US%22%3ESorted%20-%20many%20thanks!%3C%2FLINGO-BODY%3E
New Contributor
ChartMAXMAX-NDiscoverReveal
A2MAX   
A3 MAX-N  
A4  Discover 
A5MAX   
A6 MAX-N  
A7  Discover 
A8   Reveal
A9   Reveal
5 Replies

@Andy_Laurence 

Do you want to return the single non-blank value, or all four values (blank and non-blank)?

best response confirmed by allyreckerman (Microsoft)
Solution

@Andy_Laurence 

Let's say the range is in A2:E9 and the value you want to look up is in H2.

=LOOKUP(REPT("z",255),INDEX($B$2:$E$9,MATCH(H2,$A$2:$A$9,0),0))

@Andy_Laurence 

As an alternative - using the same setup as @Hans Vogelaar:

=CONCAT(FILTER($B$2:$E$9,$A$2:$A$9=H2))