Forum Discussion

ancasa's avatar
ancasa
Copper Contributor
Jan 28, 2025

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)
  • Harun24HR's avatar
    Harun24HR
    Bronze 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)
  • Patrick2788's avatar
    Patrick2788
    Silver 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)
    )

     

    • ancasa's avatar
      ancasa
      Copper 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!!

Resources