Lookup

[Tony_1066]

Hi, I'm hoping someone can help.  I have 3 columns of data (Customer, Discount & Discount %).

As there is a lot of data, I am trying to convert this to a table

I need to basically cross reference customer (Column A) & discount (Row 1)  and the result be the corresponding cell as below:

I hope this makes sense and any help would be greatly appreciated as it is driving me crazy :)

[#2]

@Tony_1066 

Why dont you make a Pivot Table?

Insert->Pivot Table

Customer in rows

Discount in column

Discount% in Value

Copy the pivot table  and change the function in the copy to Count if you want to make sure you don't have duplicates.

[#3]

@Tony_1066

Select the data.

On the Data tab of the ribbon, in the Get & Transform Data group, click 'From Table/Range'.

If your data aren't formatted as a table yet, Excel will ask whether your range has headers. Make sure that the check box is ticked, then click OK.

The Power Query Editor will open.

Select the Discount column in the data preview.

Activate the Transform tab.

Click 'Pivot Column' in the 'Any Column' group.

Specify Discount % as the column to use for the values.

Click OK.

Activate the Home tab of the Power Query Editor again.

Click Close & Load.

[#4]

If you’re looking to ask a question or start a conversation about Excel, you’re in the right place! Ask away. Please include the following info to help others answer your question: https://techcommunity.microsoft.com/t5/excel/welcome-to-your-excel-discussion-space/m-p/2204395

[#5]

Hi Mindre, thanks for the quick response, This has worked perfectly, many thanks for your help.