How to Use the LOOKUP Function in Excel
The LOOKUP function is incredibly useful for finding specific values within a cell range in Excel or Google Sheets. It is primarily used to locate a value in a single row or column and then return a corresponding value from the same position in a second row or column.
Basic Syntax
The basic syntax for the LOOKUP function is as follows:
LOOKUP(lookup_value, lookup_vector, result_vector)lookup_value: The value you are searching for within the lookup_vector.lookup_vector: The range of cells containing the target value.result_vector: The range of cells from which the corresponding result is pulled.
Example 1: Using LOOKUP for Exact Match
Consider a scenario where you have a list of student names in column A and their respective scores in column B. You need to find the score for a specific student named ‘John’.
| Student | Score |
|---|---|
| Anna | 85 |
| John | 92 |
| Mary | 88 |
To find John’s score, you can use the following formula in Excel or Google Sheets:
=LOOKUP("John", A2:A4, B2:B4)This formula searches for “John” within the range A2:A4 and returns the score from the corresponding position in the range B2:B4, which is 92 in this case.
Example 2: Using LOOKUP for Approximate Match
The LOOKUP function can also handle approximate matches if the lookup_vector is sorted in ascending order. For example:
| Grade | Letter |
|---|---|
| 60 | D |
| 70 | C |
| 80 | B |
| 90 | A |
To find the letter grade for a score of 75, you would use the formula:
=LOOKUP(75, A2:A5, B2:B5)This returns ‘C’ because 75 is the highest score in the range A2:A5 that does not exceed 75.
By mastering the LOOKUP function in Excel or Google Sheets, you can efficiently search for and retrieve specific values from your spreadsheets.
More information: https://support.microsoft.com/en-us/office/lookup-function-446d94af-663b-451d-8251-369d5e3864cb