Say you want to assign a value to a data item with a VLookUp. The data item is a number that will be in a range, like a score of 87 on a test earning a B where the range was from 81-90.
Use a VLookUp without a Range-Lookup value. First create your lookup table with the lowest end of the range for each one, so if 61-70 was a D, 71-80 a C, 81-90 a B and 91+ an A, column 1 of your table would be 0, 61, 71, 81, 91, with the next column, F, D, C, B, A, respectively. If your grade were in D1 and your table in A1:B5, your VLookUp would read =VLookUp(D1,$A$1:$B$5,2).
- Pivot Tables: Crunch the numbers using 3 tools in Excel
- Do your users open workbooks that ask if they want to update links?
- Date troubles in Excel? Inconsistent date formats in column are frustrating
- Go beyond Excel filtering with advanced techniques
- Do others sometimes accidentally delete the Excel formulas you spent hours creating?