Using vlookup in a range

 Imagine you are looking for the perfect sparkling wine. You have a database that looks like this.

The set has thousands of bottles to chose from. You want to begin the process by eliminating those deemed too cheap or too expensive. Your price ranges are as follow.

 
Your function should tell excel: Go look at the price that is stored in cell "G2", get it and find it in "Table array". Once you find it, bring the value stored in column 2 of "Table array". 

One problem is that vlookup can not understand "$15 - up". vlookup can look for only 1 value, so the first thing you want to do is to change those price ranges to a single price that vlookup could read.  Like the one below.

In red you see what does not work, in green what excel understands. It is key that the table array is sorted from low to high!

Excel will look place every price between $0 and $7 as "Cheap", anything between $7 and $15 as value... and so on. So in practice you create ranges in a vertical way.

The formula would look like this.