Articles about looking up data in Excel are written all the time. Indeed, several such articles have appeared in FM magazine.
I should know. I wrote them.
Those articles covered numerous ways to find data in Excel, but there is one common problem I have yet to explore: How do you look up data over a range?
Let’s find out.
A sample example
Imagine you are marking an exam, scoring candidates’ marks out of 100, which has the following grading system defined. Any score of 85 or above gains an “A*” grade, a score between 70 and below 85 (84 for simplicity) rates an “A”, a score between 60 and under 70 scores a “B”, and so on, as shown in the screenshot “Exam Marks” (below).
There is a problem here. The scores in the Range column are viewed as text by Excel, but the scores achieved by candidates are numerical. How do I explain to Excel that the numerical value of 57 is in the text range “50–59”? It’s simple to us, but Excel requires assistance. Therefore, we need to address two connected issues:
We need to convert the text ranges into a numerical register that may be used as a grading barometer.
We also need to decide which Excel lookup function (given there are so many) is best placed to return the required grade.
Clearly, I need to deal with the first issue first, so let’s start there. I note the scores are in ascending value. Therefore, I can just use the first value in the range to derive the “bucket”, as Microsoft likes to call these bands/ranges of values.
In Power Query, it’s trivial to return the first numerical value (there is a button you can simply click), but I am going to assume we wish to achieve this formulaically in Excel, so I may amend the ranges in real time without needing to hit a “Refresh” button. Thus, I proceed as follows, beginning with a “Helper” column in my table, as shown in the screenshot “Helper Column Added” (below).
In cell G13 in the illustration “Helper Column Added”, I have implemented the following formula:
Not my longest calculation ever by any means, but it still needs explaining. It all centres on the FIND function:
This formula looks for the character position of the hyphen ("-") in cell F13. In the text string “0-19” (the contents of cell F13), the hyphen is clearly the second character in the text string, so FIND("-",F13) will return the value two (2).
If there is no hyphen, the error message #VALUE! is returned instead. Hence, we “wrap” this formula in an IFERROR expression as follows:
IFERROR evaluates FIND("-",F13). If it returns a numerical value, that’s fine; if it returns an error, it instead searches for a plus (“+”) symbol in the same cell:
This is required because of the value in cell F20, ie, “85+”. Here, FIND("-",F20) would return #VALUE!, but FIND("+",F20) — the formula to calculate if the primary calculation results in an error — would return three (3), as the plus symbol is the third character in this text string.
Therefore, the formula:
will return the text equivalent of the first number in the range for all buckets. For example, in row 20, IFERROR(FIND("-",F13),FIND("+",F13)) returns three (3), so the formula resolves to:
=LEFT(F20, 3 – 1)
which would return the two “left-most” characters in the text string “85+”, ie, “85”. However, this would not be a numerical value, so multiplying by one (1) converts these text strings into numerical values, hence the final formula:
This has now addressed our first issue. We now have a numerical register (a list of values in increasing order) we may use to look up our students’ marks. I now need to consider which function to use to look up the data.
As with all things in Excel, simplest is best. Yes, we have VLOOKUP, HLOOKUP, INDEX MATCH, OFFSET MATCH, SUMIF, SUMIFS, SUMPRODUCT, XLOOKUP, et al., but whenever you have data in ascending order in a spreadsheet, your simplest, most reliable function to use is nothing more than the extremely humble LOOKUP function.
LOOKUP has two forms: an array form and a vector form. Let me explain the jargon:
- An array is a collection of cells consisting of at least two rows and at least two columns.
- A vector is a collection of cells across just one row (row vector) or down just one column (column vector).
The diagram “Array and Vectors” (below) should be self-explanatory.
The array form of LOOKUP looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the same array:
- lookup_value is the value that LOOKUP searches for in an array. The lookup_value argument can be a number, text, a logical value, or a name or reference that refers to a value.
- array is the range of cells that contains text, numbers, or logical values that you want to compare with lookup_value.
The array form of LOOKUP is very similar to the HLOOKUP and VLOOKUP functions. The difference is that HLOOKUP searches for the value of lookup_value in the first row, VLOOKUP searches in the first column, and LOOKUP searches according to the dimensions of array.
If the array covers an area that is wider than it is tall (ie, it has more columns than rows), LOOKUP searches for the value of lookup_value in the first row and returns the result from the last row. Otherwise, LOOKUP searches for the value of lookup_value in the first column and returns the result from the last column instead. This is why it is dangerous to use, and it is usually safer to adopt its sibling variant, the vector form instead:
LOOKUP(lookup_value, lookup_vector, [result_vector])
The LOOKUP function vector form syntax has the following arguments:
- lookup_value is the value that LOOKUP searches for in the first vector.
- lookup_vector is the range that contains only one row or one column.
- [result_vector] is optional — if ignored, lookup_vector is used — this is the where the result will come from and must contain the same number of cells as the lookup_vector.
Like the default versions of HLOOKUP and VLOOKUP, lookup_value must be located in a range of ascending values; ie, where each value is greater than or equal to the one before. If this rule is followed, LOOKUP will return the value occurring to the final occurrence of the lookup_value (whereas MATCH would return the first occurrence).
Returning to the range lookup
Given our lookup data is in ascending order, we have remarkably little left to do, to find our corresponding grade. LOOKUP in vector form works very well with our Helper column, as shown in the screenshot “LOOKUP in Vector Form With Helper” (below):
My formula in cell H28 is given by:
LOOKUP(F28,G13:G20,H13:H20) finds the largest value less than or equal to F28 (87 in the graphic) in the cell range G13:G20, which consists of the values 0, 20, 36, 43, 50, 60, 70, and 85. The largest value less than or equal to 87 is 85. It then looks for the corresponding value in the cell range H13:H20 (which would be the grades), to return the corresponding grade of A*.
The IF(F28<G13,H13,… statement is simply used to ensure that if the score in cell F28 is less than zero (the value in cell G13, and certainly not a good exam result!), the bottom grade of U (cell H13) is used instead. Since the list is in ascending order, we know for certain that the value in cell G13 represents the lowest value in the cell range G13:G20.
Word to the wise
Looking up data in ranges is a common problem with a relatively simple solution. You just need to step out the requirement by converting the range to a numerical value that may be used for comparisons and then using an appropriate lookup function. Always aim to get your lookup data into a logical, sequential order. If you achieve this, simple, staid, and stolid functions such as LOOKUP can be readily applied by modellers and easily understood by end users alike.
Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling and Continuing Financial Modelling. Send ideas for future Excel-related articles to him at email@example.com. To comment on this article or to suggest an idea for another article, contact Oliver Rowe at Oliver.Rowe@aicpa-cima.com.
“Calculating Weighted Averages in Excel”, FM magazine, 7 March 2022
Advanced Excel: Practical Applications for Accounting Professionals
This online video course will make you excel in Excel. It’s designed for accountants, by an accountant, so you’ll learn how to unlock all the magic in your spreadsheets. Using live workbook files that you can edit as you go, you’ll become a master in topics such as advanced pivot tables, external data ranges, and the IF function, and features such as process automation. All of which will help you work faster and smarter.