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.