Sumproduct Help, I think.
 

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.

Assuming that the match is unique, then

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

--
HTH

Bob Phillips

"tamato43" wrote in message
...
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.

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






"tamato43" wrote:

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.

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


Regards,

Peo Sjoblom


"Bob Phillips" wrote:

Assuming that the match is unique, then

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

--
HTH

Bob Phillips

"tamato43" wrote in message
...
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.

Thank you! Thank you! Thank you!

"Bob Phillips" wrote:

Assuming that the match is unique, then

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

--
HTH

Bob Phillips

"tamato43" wrote in message
...
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.

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" wrote:

Assuming that the match is unique, then

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

--
HTH

Bob Phillips

"tamato43" wrote in message
...
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.

Hi Peo,

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

Bob

"Peo Sjoblom" wrote in message
...
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" wrote:

Assuming that the match is unique, then


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

--
HTH

Bob Phillips

"tamato43" wrote in message
...
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.