Horizontal lookup when cell is not blank

[Enrique Rod]

Hi, I have the results of a survey with 4,500 responses, the report groups respondents with similar characteristics in different columns as shown below. I'm looking for a way to retrieve all responses for each question in one column. I've tried Hlookup with CountA, but it's just not working.

• Each question may include its own responses since some of them are multiple choice, drop-down, etc.
• Depending upon the characteristics of the respondent (example: Marital Status) , some might get additional questions. See Question 1-D highlighted in yellow as an example. If the respondent doesn't fall in the logic for the additional question, the system doesn't include the question in blank in the report, just doesn't include it at all, making the hlookup quite more difficult.
• More than one response appears in the same column (see respondent 2 and 4 as an example).
• I'm using Excel 2019

Any help would be very much appreciated.

Raúl

 

[Fluff]

I'm using Excel 2019

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=IFERROR(INDEX($C5:$L5,AGGREGATE(15,6,(COLUMN($C$4:$L$4)-COLUMN($C$4)+1)/($C$4:$L$4=M$4)/($C5:$L5<>""),1)),"")

 

[Enrique Rod]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=IFERROR(INDEX($C5:$L5,AGGREGATE(15,6,(COLUMN($C$4:$L$4)-COLUMN($C$4)+1)/($C$4:$L$4=M$4)/($C5:$L5<>""),1)),"")

After some data arrangements, this worked perfectly, Thank you!!

 

[Fluff]

You're welcome & thanks for the feedback.