Perform an inexact lookups (return a category/band)

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

An inexact Lookup allows us to define categories (by thresholds) and then lookup a value within these thresholds to return the category that our lookup value falls into. Perhaps an obvious example of this would be in terms of marks and grades – in the Topic Example Workbook above we have a series of exam marks and then in a separate table to the right a list of the lowest mark required to achieve each Grade (so any mark 80 or above is an A, anything between 70 and 79 is a B, 50 and 69 is a C and so on). It’s important that our grading structure is set to the lowest value of each threshold so in this case the lowest mark is 0.

We then have a list of marks (in Column B) which we want to lookup (against Columns G and H) and return the relevant grade in Column C. This can be achieved using an inexact lookup formula.

To do this we can use a VLOOKUP formula exactly as we did in the topic Using a VLOOKUP Function, though changing the last parameter in the function from FALSE (Exact match) to TRUE (Inexact Match).

The syntax of the VLOOKUP Formula is =VLOOKUP(“Lookup_Value”,”Table_Array”,”Col_index_Num”,”[Range_Lookup]”).

Our lookup value is the mark (e.g. the value in cell B2), our table Array is the range we want to look up in (e.g. G:H), the column index is the number of columns away from the first Column in our lookup range (Col G) the column you want to return values from is (Column H)- in this case 2.

So our completed VLOOKUP formula in cell C2 becomes =VLOOKUP(B2,G:H,2,TRUE) which returns the grade F indicating that the mark of 15 is greater than the lower threshold for grade F of 0 but less than the lower threshold for grade E which is 30.

As an alternative to a VLOOKUP we can use an inexact INDEX/MATCH function again just replacing the parameter with 1 (indicating that we want to return the first value less than) instead of 0 (indicating that we want to lookup an exact match).
The syntax of the MATCH Function is =MATCH(“Lookup_Value”,”Lookup_Array”,”Match_Type”)

Our Lookup value is the mark (e.g. cell B2), the lookup array is the column we want to find the mark in (Column F) and the match type is 1 indicating that we want a ‘Less than’ inexact match type. The INDEX Function =INDEX(“array”,”row_num”,”Column_num”)] then takes the column with the grades (Column G) as the array, the result from our MATCH as the row number and 1 as the Column number.
So our completed inexact INDEX/MATCH Function becomes =INDEX(G:G,MATCH(B2,F:F,1),1).

In cells C2 to C6 of the Topic Example Workbook, return the grade corresponding to each relevant mark in column B using a VLOOKUP function as per above.

In cells D2 to D6 do the same thing using an INDEX/MATCH function.

A demonstration is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.