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.