Value between or search in range and return value

问题:

Hi All,

I have a table for employee scores levels (Table D1 to F6), there is points for each level, for example score (4.60) will be in the 3rd level which has (3) points.

I want to write a formula in the column (B) to check in the table an return the point value from the column F, You can see the examples in D2 & D3.

Regards Adel

回答1:

What you need is one of the most awesome functions ever invented... VLOOKUP

=VLOOKUP(A2,$D$2:$F$6,3, TRUE)

(Paste in B2 and drag down)

Quick explanation of the arguments.

1. A2 is the value we're going to be looking up in the table
2. $D$2:$F$6 is the "table" we're going to be looking up. It's going to search the first column of it (You could have anything you wanted in Column D, VLOOKUP Only looks at the first column).
3. 3 is the column number of our table that contains the answers we want to be looking up, and returning (in this case, F).
4. TRUE means we want to search ranges, not just exact values. If it was FALSE then we'd only get the numbers we wanted if we entered the precise scores.

Of course, using this method, there isn't any upper bounds to it, a value of say 20 would give us the last row (i.e. 5 points), however, you could fix this easily using an IF statement.

=IF(A2 > $E$6, "N/A", VLOOKUP(A2,$D$2:$F$6,3, TRUE))

This could of course be done a bit neater if you believed you were going to be adding more rows to the table later, but it works for now.

回答2:

Use the Below formula in cell B2:

 =VLOOKUP(A2,$D$2:$F$6,3, TRUE)