Help with a Formula

[Curtain_Trend]

I'm trying to input deductions for a length according to which heading style and which track we will be using. Line 4 is trying to do the calculations accordingly as the track is column L - King Track, the heading is column B - Double Pinch Pleat and it is Face Fit.

The length is in this instance 2300. The deductions for Face Fix is 0, so the outcome should be 2300.

Screen Shot attached with the static data and the required allowances for each scenario.

I'm not sure if this is possible as there seems to be a lot of values required.

Thank you in advance. I do hope for an answer.

[#2]

@Curtain_Trend 

Hi, it's a bit difficult to answer your question if you don't show us the worksheet ("Home") where some of the search terms are located. Also, the term "Double Pinch Pleat" does not appear in the table column S in Static Data. Only in column M. I did not understand the connection between column M and the table columns Q, R, S and T.

I'll try to answer the question in general terms. If you search for several cells with XLOOKUP(), you can join them beforehand with an '&' sign. But then you have to join the columns in which you are searching. It could look like this:

=XLOOKUP(L4 & N4 & F4,
  'Static Data'!Q$3:Q$38 & 
  'Static Data'!R$3:R$38 & 
  'Static Data'!S$3:S$38,
'Static Data'!T$3:T$38,"")

This also works with VLOOKUP(), but is a little more complicated. Then you first have to join the search columns and the result columns. This can be done with CHOOSE(), for example.

=VLOOKUP(L9&N9&F9,CHOOSE({1,2},'Static Data'!Q$3:Q$38&'Static Data'!R$3:R$38&'Static Data'!S$3:S$38,'Static Data'!T$3:T$38),2,FALSE)

If you do not have XLOOKUP() you can also use INDEX with MATCH():

=INDEX('Static Data'!T$3:T$38,MATCH(L7&N7&F7,'Static Data'!Q$3:Q$38&'Static Data'!R$3:R$38&'Static Data'!S$3:S$38,0))

As your result always returns a single hit, you can also use SUMIFS() or FILTER().

I have made you a couple of examples in the attached file.

I hope this puts you on the right track.