Sumproduct Help, I think.

[Guest]

I'm not sure what the best way to do this, but maybe someone can shed some
light.

I've created a worksheet that consist of:

(Airline Discount Data-sheet 1)
A B C D
Vendor Start Price End Price Discount %
1 Continental 0 $250 5%
2 Continental $251 $500 8%
3 Continental $501 $1000 19%
4 American 0 $250 5%
5 American $251 $500 8%
Etc.. Etc..

On a different page I've created a form to call the data from this sheet.
(Discount tool-Sheet 2)

So I would manually input the following info into the cells:

A1 B1 C1
American $400.00

Leaving Cell C1 Blank.

This is my dilemma, how can I formulate cell C1 to do the following function:

Search "Sheet 1" to find American in Column A, match the Price range between
Column B And C, and provide the matching dicount percentage from column D.

Any help would be greatly appreciated.

 

[Bob Phillips]

Assuming that the match is unique, then

=SUMPRODUCT(--(A1=Sheet1!A1:A20),--(B1>=Sheet1!B1:B20),--(B1<=Sheet1!C1:C20)
,Sheet1!D120)

 

[Guest]

You only need to use column B

0
251
501
0
251
501
etc

the percentage in adjacent cells would look like

5%
8%
19%
5%
8%
19%
etc

then you could use trhis formula in C1

=INDEX(OFFSET(Sheet1!$C$1,MATCH(A1,Sheet1!$A$1:$A$20,0)-1,,3,),MATCH(B1,OFFSET(Sheet1!$B$1,MATCH(A1,Sheet1!$A$1:$A$20,0)-1,,3,)))


Regards,

Peo Sjoblom

 

[Guest]

Since the OPs example was 400 thus an inbetween value sumproduct will not work


Regards,

Peo Sjoblom

 

[Guest]

Thank you! Thank you! Thank you!

Assuming that the match is unique, then

=SUMPRODUCT(--(A1=Sheet1!A1:A20),--(B1>=Sheet1!B1:B20),--(B1<=Sheet1!C1:C20)
,Sheet1!D120)

 

[Guest]

Sorry Bob, I guess I got a shortcircuit in my brain, no need to make things
as difficult as I did, your solution works fine except when a value would be
higher than the last value per vendor, for instance if the amount would be
1001, but maybe that is not an options

Regards,

Peo Sjoblom

 

[Bob Phillips]

Hi Peo,

I must admit as well as assuming unique, I assumed integrity of data .

Bob