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.