Looking for a formula which uses multiple references in the same column & outputs value in corresponding column

[daniel_san]

Hi, I hope somebody can help with this. I receive raw data in the below format every month which I have no control over.

However I am restructuring it to make it more user friendly and I'm building a template in excel.

As part of the restructuring of the data I need to carry out, I need to reference the data first by the question type (column A: "Consideration: To what extent...etc."), then the brand (column A: "Dublin's Best etc.") and then the response (column A: "I am very unlikely... etc.").

Finally the output would be the corresponding figure in column B.

For example: The percentage for those that would be very unlikely to consider purchasing from Farmer's Produce would return the result of 35%.

What formula would I use to to achieve this result? I have tried Index Match, hlookups, vlookups, sumproducts etc. and I have come to the conclusion that I am just not smart enough to work it out.

Essentially I need something that keeps looking down column A, finds the question, then continues looking down until it finds the correct brand, continues down again until it hits the correct response, then outputs the corresponding answer in column B.

Any help with this would be massively appreciated as it has been annoying me for quite some time.

Thanks in advance.

[TABLE="class: grid, width: 700, align: left"]
<tbody>[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Consideration: To what extent would you consider buying from these brands in the future?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Dublin's Best[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Total[/TD]
[TD]N=503[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]I am very unlikely to consider purchasing from this brand in the future[/TD]
[TD]31%[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]I am somewhat unlikely to consider purchasing from this brand in the future[/TD]
[TD]17%[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]I may or may not consider purchasing from this brand in the future[/TD]
[TD]42%[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]I am somewhat likely to consider purchasing from this brand in the future[/TD]
[TD]8%[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]I am very likely to consider purchasing from this brand in the future[/TD]
[TD]3%[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Farmer's Produce[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Total[/TD]
[TD]N=503[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]I am very unlikely to consider purchasing from this brand in the future[/TD]
[TD="align: right"]35%[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]I am somewhat unlikely to consider purchasing from this brand in the future[/TD]
[TD="align: right"]12%[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]I may or may not consider purchasing from this brand in the future[/TD]
[TD="align: right"]30%[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]I am somewhat likely to consider purchasing from this brand in the future[/TD]
[TD="align: right"]20%[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]I am very likely to consider purchasing from this brand in the future[/TD]
[TD="align: right"]3%[/TD]
[/TR]
</tbody>[/TABLE]