Forum Discussion
SKU Price Based on Customer
Hi all, first time posting to the forum. I've been trying to find an answer to this, but for whatever reason I just can't seem to find anything, maybe I'm not searching for the right keywords.
I have a table that shows me for each SKU how many units were made (Excel table) and I have another Excel table that has the ASP for each SKU, however some of the SKUs have different prices based on the customer. What I need is a way to create a new column in the first table that pulls in the correct ASP for that SKU based on the customer. For some reason the relationships between the tables aren't working the way I want them to. Below is what I mean:
Table 1:
| SKU | Serial Number | Date Produced |
| 12345 | ABC123 | 1-Jun-24 |
| 45678 | ABC124 | 1-Jun-24 |
Table 2:
| SKU | Customer | ASP |
| 12345 | Acme | $10 |
| 12345 | Cobalt | $12 |
| 45678 | Acme | $15 |
| 45678 | Cobalt | $18 |
Table 1 Modified:
| SKU | Serial Number | Date Produced | ASP (Based on Customer) |
| 12345 | ABC123 | 1-Jun-24 | $10 |
| 45678 | ABC124 | 1-Jun-24 | $18 |
Thanks in advance for any help.
3 Replies
- mh2587
Super User
//Try this & I am assuming that there is a relationship between Table 1 and Table 2 based on SKU ASP = LOOKUPVALUE( 'Table 2'[ASP], 'Table 2'[SKU], 'Table 1'[SKU], 'Table 2'[Customer], 'Table 1'[Customer] )Thanks very much for the quick response. There is a relationship between the tables based on the SKU, however it's many to many as there's multiple values for each SKU. With the solution you posted the challenge is that Table 1 doesn't have the Customer listed so I'm not sure how that final value in the formula would work. Now that I think about it I won't be able to do what I want to do without having the Customer in table 1, that's the issue. If I can get that figured out then your solution should work.
Thanks very much for the help!
