 SOLVED

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

 Chart MAX MAX-N Discover Reveal A2 MAX A3 MAX-N A4 Discover A5 MAX A6 MAX-N A7 Discover A8 Reveal A9 Reveal
5 Replies

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

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

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

@Hans VogelaarThanks. The single non-blank value.

best response confirmed by allyreckerman (Microsoft)
Solution

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

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

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

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

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

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

Sorted - many thanks!