SOLVED

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

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))