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.