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.