LOOKUP function

Excel LOOKUP Function allows you to search for a value in a specific range or table and return a corresponding value from another range or table. It’s like finding information in a big book and using a reference to get the answer.


=LOOKUP(lookup_value, lookup_vector, result_vector)


Here’s a breakdown of the function’s arguments:

lookup_valueThe value you want to find in the lookup_vector.
lookup_vectorThe range of cells where Excel will search for the lookup_value.
result_vectorThe range of cells from which Excel will return a corresponding value.

How to use

Let’s break down how to use the LOOKUP function with an example:

Imagine you have a list of student names in one column and their corresponding test scores in another column. You want to find a specific student’s test score.

In this example:

  • “John” is the lookup_value, which is the student’s name you’re searching for.
  • A1:A5 is the lookup_vector, the range of student names.
  • B1:B5 is the result_vector, the range of test scores.

The LOOKUP function will search for “John” in the student names, and when it finds a match, it returns the corresponding test score. If “John” is in A3, it will return the test score from B3.

Here are a few things to keep in mind:

  • The LOOKUP function works best when the data in the lookup_vector is sorted in ascending order. If it’s not, you may not get the desired result.
  • If Excel doesn’t find an exact match, it will return the closest match that is less than or equal to the lookup_value. So, if “John” isn’t in the list, it will return the score of the nearest name that comes before “John” in alphabetical order.

Now, let’s see what happens if we use the LOOKUP function with a value that isn’t in the list:

If “Sarah” isn’t in the student names, the function will return the test score of the student with the name closest to “Sarah” alphabetically. So, it’s crucial to ensure your data is correctly sorted.

The LOOKUP function is a useful tool for finding data in large lists or tables, but it requires careful data organization to work effectively.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.