The LOOKUP function in Microsoft Excel is a powerful tool that allows you to search and retrieve information from a range of data. This function is capable of performing lookups based on the value you provide and returning the corresponding result from a different data range. This is useful when working with large data tables or when you need to determine a specific value based on certain conditions. Below, we will explore in detail how to use the LOOKUP function along with a detailed example on a computer to illustrate!
1. What is the LOOKUP function in Excel?
The LOOKUP function is a function in Microsoft Excel used to search for a value in a range or table and return the corresponding value based on a match. The LOOKUP function can be used to look up data or substitute different values based on the condition you set.
2. LOOKUP function formula
The LOOKUP function in Excel can be used in two forms: Array Form and Vector Form. Here is a guide on how to use both forms:
1. Using Array Form
The array form of the LOOKUP function allows you to search for a value in a data range and return the corresponding value from a different range. Here’s how to use it:
Formula:
=LOOKUP(lookup_value, lookup_vector, result_vector)
Where:
- lookup_value: The value you want to search for.
- lookup_vector: The data range in which you want to search for the value.
- result_vector: The data range that contains the value you want to return when a value is found.
Common characteristics:
- Used to search for a value in a data range and return the corresponding value from a different range.
- Data does not need to be sorted.
- The LOOKUP function uses a value to compare with the values in the lookup_vector and returns the corresponding value from the result_vector.
Example: Suppose you have two lists: one list containing the names of people and one list containing the corresponding phone numbers. The data could be as follows:
Now, you want to find the phone number of the person with the name A. You can use the LOOKUP function as follows:
=LOOKUP(“A”, A2:A5, B2:B5)
The result of the above formula will be the phone number corresponding to the name A, which is 123-456-7890.
2. Using Vector Form
The vector form of the LOOKUP function allows you to search for a value in a sorted data range and return the corresponding value from a different range. Here’s how to use it:
Formula:
=LOOKUP(lookup_value, lookup_array, result_array)
Where:
- lookup_value: The value you want to search for.
- lookup_array: The data range that has been sorted in ascending order in which you want to search for the value.
- result_array: The data range that contains the value you want to return when a value is found.
Common characteristics:
- Used to search for a value in a sorted data range and return the corresponding value from a different range.
- Data needs to be sorted in ascending order.
- The LOOKUP function uses a value to compare with the values in the lookup_array and returns the corresponding value from the result_array.
Example: Suppose you have a list of products and their prices in a data table as follows:
Formula using the LOOKUP function:
You want to find the price of the product Tablet. Using the Vector Form of the LOOKUP function, you can do the following:
=LOOKUP(“Tablet”, A2:A5, B2:B5)
The result of the above formula will be the price of the Tablet product, which is 300 USD.
The vector form of the LOOKUP function allows you to quickly search for a value within a range of data and return the corresponding result from the same range of data.
3. Additional Information about the LOOKUP function
The LOOKUP function can work with number ranges or text ranges, and can be used with different types of ranges such as dates, times, etc. The LOOKUP function requires the range or table you are looking up to be sorted in ascending order.
If there is no match or the lookup_value is outside the range or table of the lookup_vector, the LOOKUP function will return the closest value in the lookup_vector.
The LOOKUP function is one of the powerful tools in Excel for searching and extracting information from large datasets.
This recent article has helped you to understand in detail and how to use the LOOKUP function in Excel extremely simple and fast. If you find it useful, you can share this article with your loved ones and friends!
How to recover unsaved or overwritten Excel files effectively: a sure-fire method
Knowing how to recover unsaved Excel files can be extremely helpful in cases when your computer unexpectedly shuts down or you accidentally click on “Don’t save” when Excel asks if you want to save your changes before closing. Follow this article to learn the step-by-step process of how to accomplish it.
5+ Effective Ways to Convert PDF to Excel Quickly, Useful Office Tricks
Excel Quickly, Useful Office Tricks’>
Knowing the ways to convert PDF files to Excel below will enable you to easily edit and add content inside. Office workers and students definitely must know this trick if they want to simplify their tasks.