logo-v-light

Our Service

LOOKUP

In this comprehensive guide, we will explore everything you need to know about the LOOKUP function in Excel. The LOOKUP function is a powerful tool that allows you to search for a value in a specified range or array and return a corresponding value from another range or array. This function is particularly useful when you need to find specific information in large datasets or when you want to cross-reference data between different tables.

LOOKUP Syntax

The LOOKUP function has two different syntax forms: Vector form and Array form.

Vector form:

LOOKUP(lookup_value, lookup_vector, [result_vector])

Arguments:

  • lookup_value: The value you want to search for in the lookup_vector.
  • lookup_vector: The range or array containing the values you want to search.
  • result_vector (optional): The range or array containing the values you want to return. If omitted, the function will return the value from the lookup_vector.

Array form:

LOOKUP(lookup_value, array)

Arguments:

  • lookup_value: The value you want to search for in the array.
  • array: The range or array containing both the lookup values and the result values.

LOOKUP Examples

Let’s explore some examples of how to use the LOOKUP function in Excel.

Example 1: Basic LOOKUP in vector form

Suppose you have a list of products and their prices, and you want to find the price of a specific product. You can use the LOOKUP function in vector form to achieve this:

=LOOKUP(“Product B”, A1:A10, B1:B10)

In this example, the function searches for “Product B” in the range A1:A10 and returns the corresponding price from the range B1:B10.

Example 2: Basic LOOKUP in array form

Using the same product and price data as in Example 1, you can also use the LOOKUP function in array form:

=LOOKUP(“Product B”, A1:B10)

In this case, the function searches for “Product B” in the first column of the array A1:B10 and returns the corresponding price from the second column.

Example 3: LOOKUP with approximate match

Suppose you have a list of scores and corresponding letter grades, and you want to find the letter grade for a specific score. You can use the LOOKUP function with an approximate match:

=LOOKUP(85, A1:A5, B1:B5)

In this example, the function searches for the value 85 in the range A1:A5, which contains the score thresholds. If an exact match is not found, the function returns the corresponding letter grade from the range B1:B5 for the nearest lower value.

LOOKUP Tips & Tricks

Here are some tips and tricks to help you get the most out of the LOOKUP function:

  • Ensure that your lookup_vector or the first column of your array is sorted in ascending order. This is especially important when using approximate matches.
  • If you need to perform a case-sensitive lookup, you can combine the LOOKUP function with the EXACT function.
  • Use the IFERROR function to handle errors, such as when the lookup_value is not found in the lookup_vector or array.

Common Mistakes When Using LOOKUP

Here are some common mistakes to avoid when using the LOOKUP function:

  • Not sorting the lookup_vector or the first column of the array in ascending order, which can lead to incorrect results.
  • Using the wrong syntax form (vector or array) for your specific use case.
  • Forgetting to include the result_vector in the vector form when you want to return a value from a different range or array.

Why Isn’t My LOOKUP Function Working?

If your LOOKUP function is not working as expected, consider the following troubleshooting steps:

  • Check that you have used the correct syntax form (vector or array) for your specific use case.
  • Ensure that your lookup_vector or the first column of your array is sorted in ascending order.
  • Verify that your lookup_value, lookup_vector, and result_vector (if applicable) are correctly specified and that there are no typos or errors in your formula.
  • Use the IFERROR function to handle errors, such as when the lookup_value is not found in the lookup_vector or array.

LOOKUP: Related Formulae

Here are some related formulae that you might find useful when working with the LOOKUP function:

  • VLOOKUP: Searches for a value in the first column of a table and returns a value in the same row from a specified column.
  • HLOOKUP: Searches for a value in the first row of a table and returns a value in the same column from a specified row.
  • INDEX: Returns the value of a cell in a specified row and column of a range or array.
  • MATCH: Searches for a value in a specified range or array and returns the relative position of the value within the range or array.
  • XLOOKUP: A more versatile and powerful alternative to VLOOKUP and HLOOKUP, available in newer versions of Excel.

By understanding the ins and outs of the LOOKUP function, you can efficiently search for and retrieve data from your Excel spreadsheets. With this comprehensive guide, you should now have all the information you need to master the LOOKUP function and its various applications.

Related

Did you find this article useful?

Share it with your friends or colleagues

About Aepoch Advisors

We are a boutique accounting and consulting firm servicing international businesses operating in China. We offers book keeping and business advisory service. We also help our clients select and implement SAAS business applications. 
 
Cloud technology significantly reduces cost foreign companies traditionally spent on tax compliance and ERP systems. Our cloud professionals can help with streamlining your management and controling structure, as well as advising you on how to reduce risks and maximize profits with software purposely built for Chinese business. Contact us today to learn more about our services.
Learn More