Oct 03 2021 12:48 AM
Chart | MAX | MAX-N | Discover | Reveal |
A2 | MAX | |||
A3 | MAX-N | |||
A4 | Discover | |||
A5 | MAX | |||
A6 | MAX-N | |||
A7 | Discover | |||
A8 | Reveal | |||
A9 | Reveal |
Oct 03 2021 01:07 AM
Do you want to return the single non-blank value, or all four values (blank and non-blank)?
Oct 03 2021 04:33 AM
@Hans VogelaarThanks. The single non-blank value.
Oct 03 2021 05:50 AM
SolutionLet'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))
Oct 03 2021 06:54 AM
As an alternative - using the same setup as @Hans Vogelaar:
=CONCAT(FILTER($B$2:$E$9,$A$2:$A$9=H2))
Oct 11 2021 01:16 AM
Oct 03 2021 05:50 AM
SolutionLet'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))