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

daniel_san

New Member
Joined
Apr 6, 2017
Messages
8
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.

1AB
2Consideration: To what extent would you consider buying from these brands in the future?
3
4Dublin's Best
5
6Total
7TotalN=503
8I am very unlikely to consider purchasing from this brand in the future31%
9I am somewhat unlikely to consider purchasing from this brand in the future17%
10I may or may not consider purchasing from this brand in the future42%
11I am somewhat likely to consider purchasing from this brand in the future8%
12I am very likely to consider purchasing from this brand in the future3%
13
14
15Farmer's Produce
16
17Total
18TotalN=503
19I am very unlikely to consider purchasing from this brand in the future35%
20I am somewhat unlikely to consider purchasing from this brand in the future12%
21I may or may not consider purchasing from this brand in the future30%
22I am somewhat likely to consider purchasing from this brand in the future20%
23I am very likely to consider purchasing from this brand in the future3%

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Watch MrExcel Video

Forum statistics

Threads
1,122,529
Messages
5,596,695
Members
414,088
Latest member
rodriboraun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top