Pricing Matrix & Monday Brain Fog

[msquared99]

Hello fellow excel users!

This morning I am having brain fog and need a little help. Perhaps I am overthinking this.

I have a pricing scale where in range B2:B7 I have the following:
1,2,3,4,5,All

In range C1:G1 (Qty size) I have 1 to 250, 251 to 1000, 1001 to 3000, 3001 to 5000, 5001+

RangeArea = B1:G7

Pricing = J1:K6 and is a range where Cells J1:J6 contains 1,2,3,4,5,All and cells K1:K6 contains 1,2,3,4,5,6

In a separate sheet I have client data where column C has the pricing scale of 1 to All and column D has the quantity size. So cell C10 = 3 and cell D10 = any number from 1 to ...

I am looking for a formula to lookup the pricing scale (1,2,3,4,5,All) and search between the quantities listed in range C1:G1.

I have three formulas in three different cells that works but I want one formula to do the job.

Here is what I have:
Column S I have =VLOOKUP(C10,Pricing,2,0) this looks for 1,2,3,4,5,All and returns a value of 1,2,3,4,5,6. 6 = All

Column T I have =LOOKUP(D10,{1,251,1001,3001,5001},{1,2,3,4,5})

Column U I have =OFFSET(RangeArea,S10,T10,1,1)

Any suggestions on combining the three formulas?

 

[shrivallabha]

You can try below approach. The formula is array formula so you have to commit by pressing CTRL+SHIFT+ENTER
=INDEX($C$2:$G$7,MATCH(J2,$B$2:$B$7,0),MATCH(K2,--LEFT(C1:G1,FIND(" ",C1:G1))))

I am attaching the sample workbook. CSE will not be required if you use values like 251 in the field.

 

[msquared99]

Thanks! That works perfect!