Vlookup for a price list based on width X height in a table

alxarutiunov 

Like this:

=IF(C2="", "", LET(
    Offs, 26*(G2-1),
    Drop, OFFSET('Rollers RRP'!$A$3:$A$26, Offs, 0),
    MaxDrop, OFFSET('Rollers RRP'!$A$26, Offs, 0),
    Width, OFFSET('Rollers RRP'!$B$2:$Y$2, Offs, 0),
    MaxWidth, OFFSET('Rollers RRP'!$Y$2, Offs, 0),
    Range, OFFSET('Rollers RRP'!$B$3:$Y$26, Offs, 0),
    IFERROR((IF(OR(D2>MaxDrop, C2>MaxWidth), 0, INDEX(Range, XMATCH(D2, Drop, 1),XMATCH(C2, Width, 1)))), 0)))

See the attached version.

HansVogelaar 

Hi Hans

Would you be so kind to have another look at the attached file - I've updated it a bit - 

so now I have split all the fabrics in different categories. 

The price category gets displayed in column G. 
I'd now like to have my formula in column H on sheet "Manual-Copy before editing" to take the respective category into account when looking up the price of the blind on sheet "Rollers RRP". 

I tried adding another Xmatch formula, but I can't get it right.

Is there a way to get it working please?

alxarutiunov 

Is there a way to conditionally format these cells so that instead of colour it would show numbers from 1 to 7? 

If you were to ask me, I'd suggest that it would make more sense to:

• first, make the references in column E into absolute references  (e.g., $J$17 for the Clear PVC price), and,
• second, use the Sort command on the Data tool bar to sort them from lowest to highest. As I've done in the attached file.  (image shown below as well)

[Showing a rank, 1 to 7, would NOT be a function of conditional formatting--by definition; that's not a matter of "format" but a different value. If you really wanted to do that, it would make more sense to have an adjacent column displaying rank, but my own reaction even to that is that a sorted display is clearer, with or without the colors.]

HansVogelaar 

Thanks, makes sense.

One more question. In the attached file - cells E2:E10 show the total pricing for 7x different ranges of fabric. I've added conditional formatting to them, so that it colours them to show cheapest/most expensive range. Is there a way to conditionally format these cells so that instead of colour it would show numbers from 1 to 7? 

alxarutiunov 

Automated solutions would require VBA code. A simpler solution might be:

• Keep the 20 rows.
• Each time you need to create a new quote, duplicate the sheet.
• Remove the unneeded rows from the duplicate.

Another one:

• Save the workbook as an Excel template (*.xltx).
• Each time you need to create a new quote, double-click the template in Windows Explorer. This will create a new workbook based on the template.

@Hans Vogelaar 

Hi team!

Can you please help with one more thing. I've attached the file with the form working just fine. The next question is - the template I created has 20 rows for separate blinds to be priced up, but I rarely need to price 20x rows for a customer. Is there an option to either:

a) leave 1x row and then have an ability to create the next rows with a button (not creating/inserting them manually) - so that all the formulas and formatting get extended.
b) leave 20x rows as it is now, but being able to automatically remove the empty rows and then resetting the spreadsheet back to 20x rows after use?

alxarutiunov 

If you have Microsoft 365 or Office 2021, you can use

=IF(OR(K3>B8,K4>H2),"N-A",INDEX(C3:H8,XMATCH(K3,B3:B8,1),XMATCH(K4,C2:H2,1)))

If you have an older version, you could reverse the order of the values in row 2 and column B, then use the formula

=IF(OR(K3>B3,K4>C2),"N-A",INDEX(C3:H8,MATCH(K3,B3:B8,-1),MATCH(K4,C2:H2,-1)))

I have illustrated both approaches in the attached version (Sheet1 and Sheet2).

HansVogelaar 

Hi Hans

Almost, but there is an issue with the numbers - it only works correctly if I choose the exact width/projection that matches the width/projection in B3:B8 and C2:H2 respectively.

For example in the attached file - the width selected is 3580, projection is 2500 - the correct price should be 2408 dollars, but it shows as 2238 - one cell up from the correct price. 

Every width and projection should be checked whether it is more than the previous price row/column, but less than the next one. So in this case - width 3580 is more than 3000, but less than 3590 - hence it should be B3 for width. Could you please amend it? 

Much appreciated.

alxarutiunov 

Does this do what you want?

mathetes 

Thanks so much for your reply.

You're correct - the 1500x900 should receive a N-A in the second sample table.

We work with a supplier who provides price lists in a form of such tables and unfortunately we have to use these, so can't really change the design/layout of the tables.

Some tables have prices for all the cells, some have not (as per example with NA for a 1500x900). I guess we can deal with that by entering N-A into the price cells.

My only question is:

The first table that Hans kindly offered works fine in terms of the formulas, but I just need to change the headings so that they read correctly - eg instead of showing the smallest possible number in the range - it should show the biggest. 

alxarutiunov 

Again, both clarity and precision matter. To take your table as an example, just the headers and first row,

Are you saying that 1510 width by 900 projection should receive an N-A? That IS the way the table would be read by the existing formulas. To be more precise, anything with width over 1500 and projection under 1000 would receive "N-A". Granted, maybe you were just giving an example, that you might need in the future to render certain combinations as 'Not available' but, sticking with the example, is that the way you were thinking that it would be understood?

My own suggestion (if I might step in and put on a design hat for a moment) is that you might want, for the sake of customer flexibility and your own pricing flexibility, want to come up with a more finely tuned table, with a greater range of cutoffs on both projection and width. But I don't know how your production facility works, don't know how much flexibility you want to give your customers.

The kind of table that HansVogelaar and I (in the first reply) have given you illustrates the possibilities; you can take the same formula and have six to ten "cutoff" points (or more) for both width and projection, IF that would be meaningful. You could also just make price a matter of square inches (or centimeters, whatever the unit of measure is that you're using), and thereby make a price that really reflects the size.....  That would involve a different formula, so IF that makes sense to you, let us know.

My basic point, since you have often asked "is thus and so possible" is that Excel is VERY flexible, able to meet all kinds of requirements; i.e., it's almost limitless what is possible. The challenge, now that you know that, is to think carefully about what kind of sizing model and pricing procedure would give you and your customers the greatest satisfaction, and thereby be the best for your business?

mathetes 

Hi

Thanks for getting back to me! Please see answers below:

minimum drop a person can select - 1 mm (it's unlikely to be selected in real life, but it is correct for the sake of the price list)
the maximum drop a person can select - will equal the biggest drop in the table - 2000 in this example, anything over it returns TOO LARGE result
the minimum width - 1 mm
maximum width - will equal the biggest drop in the table - 2000 in this example, anything over it returns TOO LARGE result
anything else? - I also need to be able to amend the table in future for different products to allow for bigger tables or have some of the measurements in the table to be invalid- see table sample below

alxarutiunov 

I had the biggest number in the range (width up to 1000) - you have the first number the in the range (width from 0) - can this be redone to have my way of displaying? 1000/1500/2000/too big?

I'll reply for HansVogelaar , just to say "Of course it can be redone." You just need to be clear on what your specs for this are. What's the

• minimum drop a person can select,
• the maximum drop a person can select,
• the minimum width
• maximum width
• anything else?

Are you able to take the framework that Hans has given you and make the changes yourself? That would be ideal, since you are going to want to be managing this going forward. Might as well figure that out now.... but if you want help doing it one more stage, just do your best to be both clear and comprehensive in what you're expecting.