Forum Discussion
ancasa
Jan 28, 2025Copper Contributor
How to lookup a value and and multiple columns and conditions
Hi! I have the following problem:
In one hand, Spreadsheet 1 has in column A, an Item with its respective average weekly demand (AWD) and in column C i need the price according to that AWD.
In the other hand, Spreadsheet 2 has the item that should match with the item in Spreadhseet 1 and the prices by tier. For example, if Item "A" has an AWD of 5400, the correct price is the tier of "5K" 342.60 because its higher than 5000 but lower than the next tier that is 10000.
I need help looking up for the correct price based on the problem stated. Appreciate your help experts!
We can use multiple formulas. Here are a few. You may download the attached file.
=XLOOKUP(B6,{5,10,25,75,100,125,150,200}*1000,XLOOKUP(A6,$G$6:$G$12,$H$6:$O$12),0,-1)
But I would suggest dynamic spill array formula using MAP().
=MAP(A6:A12,B6:B12,LAMBDA(x,y,XLOOKUP(y,{5,10,25,75,100,125,150,200}*1000,XLOOKUP(x,G6:G12,H6:O12),0,-1)))
Using REGEXEXTRACT().
=XLOOKUP(B6,REGEXEXTRACT($H$5:$O$5,"[0-9]+")*1000,XLOOKUP(A6,$G$6:$G$12,$H$6:$O$12),0,-1)
- Harun24HRBronze Contributor
We can use multiple formulas. Here are a few. You may download the attached file.
=XLOOKUP(B6,{5,10,25,75,100,125,150,200}*1000,XLOOKUP(A6,$G$6:$G$12,$H$6:$O$12),0,-1)
But I would suggest dynamic spill array formula using MAP().
=MAP(A6:A12,B6:B12,LAMBDA(x,y,XLOOKUP(y,{5,10,25,75,100,125,150,200}*1000,XLOOKUP(x,G6:G12,H6:O12),0,-1)))
Using REGEXEXTRACT().
=XLOOKUP(B6,REGEXEXTRACT($H$5:$O$5,"[0-9]+")*1000,XLOOKUP(A6,$G$6:$G$12,$H$6:$O$12),0,-1)
- Patrick2788Silver Contributor
This solution takes the data as you've arranged it and with the help of REGEXEXTRACT, converts the text headers to numbers. From there, it's a standard INDEX-XMATCH:
My formula uses named items.
=LET( scale, REGEXEXTRACT(tier, "\d+") * 1000, i, XMATCH(my_items, item), j, XMATCH(AWD, scale, -1), INDEX(amount, i, j) )
- ancasaCopper Contributor
Hi Patrick,
I'm having issues replicating the formula. Is there a possibility we can have a quick Meet meeting?
I'd appreciate!! Thank you!!