Lookup a cell based on 2 criteria

[shane24]

Good day everyone. I have a spreadsheet that is used for configuring the price of an item I am selling/leasing based on the price of the item. I have attached a sample so I don't have to explain everything in detail. It may take a while.

Basically, the cells B3, D2 & E2 are drop down lists that refer to the lease rate chart to use and the months or length of the lease. What I need is to select a chart from the list in B3. Then based on the selection in either D2 or E2 have D3 and E3 display the correct lease rate from the chart that corresponds to the month selected. The caviot is that the lease rate is based upon the total selling price. So we need to include that in the equation.

If any of you could please take a look at the sample I have posted and send me a message with any questions and help would truly appreciated.

Thanks,
Shane

[rylo]

Hi

Try

1) Create 3 defined names: I refers to C22, II refers to H22 and III refers to M22
2) D3: =OFFSET(INDIRECT($B$3),MATCH(D2,$B$23:$B$59,0),VLOOKUP($B$17,{0,1;5000,2;50000,3},2))
3) E3: =OFFSET(INDIRECT($B$3),MATCH(E2,$B$23:$B$59,0),VLOOKUP($B$17,{0,1;5000,2;50000,3},2))

HTH

rylo

[duane]

here is how I approached it given your basic setup. I first inserted a row 22above your chart data and put the following data in d21:f21 and d22:f22

0 5000 50000
4999 50000 99999

Then in cell d3, copied to e3

=IF($B$3="I",SUMPRODUCT((D$2=$C$24:$C$60)*($B$17>=$D$21:$F$21)*($B$17<=$D$22:$F$22)*($D$24:$F$60)),IF($B$3="II",SUMPRODUCT((D$2=$H$24:$H$60)*($B$17>=$I$21:$K$21)*($B$17<=$I$22:$K$22)*($I$24:$K$60)),IF(B3="III",SUMPRODUCT((D$2=$M$24:$M$60)*($B$17>=$N$21:$P$21)*($B$17<=$N$22:$P$22)*($N$24:$P$60)),"No Data for this Chart")))

[shane24]

Thank you very much for your replies. I will work with those suggestions. If I have any problems I will reply again.

Again thank you so much for your help!!

Shane