Request: How to make LOOKUPVALUE formula retrieve from multiple result columns

[PBI12345]

Hello,

I have a data table containing information on the sale of beauty products. Each row is an individual transaction record, but does not include the price paid by the customer. It does include the loyalty tier of the customer. 

In a separate lookup table, I have a list of prices based product type and loyalty tier (different loyalty tiers pay different amounts per product).

How can I easily map this price information into my data table? I have tried different combinations of LOOKUPVALUE and RELATED but can't get it to work given my lookup table has values across multiple columns:

Product	Platinum	Gold	Silver	Bronze
Blush	$35	$40	$45	$50
Mascara	$30	$35	$38	$40
Foundation	$70	$80	$90	$100
Fake eyelashes	$14	$16	$18	$20
Lipstick	$35	$40	$45	$50

Transaction ID	Sale Date	Product	Quantity	Loyalty Tier	Cost
1	1-Jan	Blush	1	Platinum	??
2	1-Jan	Blush	4	Bronze	??
3	1-Jan	Mascara	1	Bronze	??
4	1-Jan	Lipstick	2	Gold	??

Any help would be much appreciated!

Thank you 

I am trying (unsucessfully) to use LOOKUPVALUE to

[xifeng_L · Accepted solution]

Hi @PBI12345 ,

First, you need to convert the price lookup table into a one-dimensional table format in PowerQuery.

Then, we can use the LOOKUPVALUE function to match the price in Sales table.

Demo - How to make LOOKUPVALUE formula retrieve from multiple result columns.pbix

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[PBI12345]

Works perfectly! Thank you for the very fast + detailed response.