Forum Discussion
Formula to get prices from a catologue with a variable price colum
Hello,
i'm currently creating an order table and I would like it to automatically retrieve prices from the catalog based on the reference (id ordered) and the customer.
I was thinking of doing a XLOOKUP but I don't see how to retrieve the right column based on the customer
In the catalog there are about 1000 lines and the columns "customer price A", "customer price B" and "id" are spaced by several customers In my example there are only 2 customers but in reality there are more like ten.
Do you have any sugestions as to how i could solve this ?
The formula will then be replicated on about 1000 orders for the prices, and the name, so I need it to be as optimized as possible
There are couple of ways. You may try nested XLOOKUP() or FILTER() or INDEX/MATCH combinations.
=XLOOKUP(I4,$A$4:$A$7,XLOOKUP("price client " &K4,$C$3:$D$3,$C$4:$D$7,"")) =FILTER(FILTER($C$4:$D$7,$C$3:$D$3="price client "&K4,""),$A$4:$A$7=I4) =INDEX($C$4:$D$7,MATCH(I4,$A$4:$A$7,0),MATCH("price client "&K4,$C$3:$D$3,0))In L4:
=IFERROR(INDEX($C$4:$D$7, XMATCH(I4, $A$4:$A$7), XMATCH("price client "&K4, $C$3:$D$3)), "")
Fill down.
You'd have to adjust the ranges for your real worksheet.
