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).