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

[Andy_Laurence]

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

[#2]

@Andy_Laurence 

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

[#3]

@Hans VogelaarThanks. The single non-blank value.

[#4 · Accepted 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))

[#5]

@Andy_Laurence 

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

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

[#6]

Sorted - many thanks!