Thứ Hai, Tháng Hai 10, 2025
spot_img
HomeHow to use LOOKUP function in Excel - Data search function

How to use LOOKUP function in Excel – Data search function

LOOKUP function in Excel has many uses. The article will tell you How to use LOOKUP function in Excel simple and fast.

Searching in Microsoft Excel spreadsheets is quite easy using keyboard shortcuts Ctrl + F. However, you can use more sophisticated tools to find and extract data based on specific values.

Microsoft Excel provides many functions to help you do this easily, and LOOKUP is just a part of them.

What is the LOOKUP function in Microsoft Excel?

The LOOKUP function in Excel references a cell to match the value in another row or column based on that cell to give the user a corresponding result.

Some common ways to use the LOOKUP function:

  • Find exact or approximate values.
  • Data can be searched both vertically and horizontally.
  • Easier to use and no need to select the entire table.

Concepts you need to know in the LOOKUP function formula

  • Lookup – Find a specific value in a data table.
  • Lookup Value – A lookup value
  • Return Value – Value in the same location but different rows or columns depending on whether you are looking up data horizontally or vertically.
  • Master Table – The table you get the matching values ​​from.

The Lookup function will be used in two forms: vector form and array form. Each form will have different formulas and application cases. The following article will guide you how to use the Lookup function in Excel.

Instructions for using the Lookup function on Excel

Vector form of Lookup function Used to find a value in a range of 1 row or 1 column, and return a value from the same location in a second range of 1 row or 1 column. This Vector form will be used when you want to determine the range containing the values ​​you want to compare, or when the range to be searched includes many values ​​or values ​​that can change.

Array Format to look for the specified value in the first column or row of the array, then return the value from the same location in the last column or row of the array. The array format is used when the search range has few values, the values ​​remain the same and must be sorted.

1. Use the Lookup function in Vector format

The formula is =Lookup(Value to look for, Area containing the value to look for, Area containing the result value).

The value to find can be a number, text, logical value, name or reference to a value.

The area containing the value to be searched is text, number or logical value. The values ​​in this range must be sorted in ascending order to avoid errors.

The resulting value container can be 1 row or 1 column.

Note:

  • If the search value does not exist, the smallest value in the search value container will be used.
  • If the value to be searched is smaller than the smallest value in the area containing the value to be searched, the #N/A error will be reported.

We will apply the statistics table below. For example, if you want to buy a phone for about 7,500,000, what type of phone would you look for? And if you want to find a phone within 13 million, what type should you look for?

Vector Lookup function

Step 1:

In the result input box for phones in about 7,500,000 users enter the formula as =LOOKUP(7500000,C2:C6,B2:B6) then press Enter. If you use numbers, you should not use unit line separators.

Enter the Lookup formula in vector format

The result will be that the phone model is Lenovo.

Results of Lookup vector function

Step 2:

To find the phone model within 13 million, we enter the formula in the resulting box =LOOKUP(13000000,C2:C6,B2:B6) then press Enter.

The value 13000000 is not in the data area, so the Lookup function will search for values ​​less than 13000000.

Find zero values ​​in the data range

As a result, we will have an iPhone phone company that we should buy with an amount of about 13 million.

Lookup function value result

If you need to find a phone within 5 million VND, it will be error #N/A as shown below. Because the value 5,000,000 is smaller than the smallest value in the table which is 6,000,000, an error is reported. The desired value will only range from 6,000,000 to 12,000,000.

Report an error in the vector Lookup function

2. Use the array Lookup function

The syntax is =LOOKUP(Value to look for, Search area).

The value to find is the value the Lookup function needs to find in an array.

The search area is the range of cells containing text, numbers, and logical values ​​that need to be searched.

Note:

  • If the desired value is not found, the nearest smaller value in the search area will be taken.
  • If the value to look for is smaller than the smallest value in the first row or column, the Lookup function will return the #N/A error.
  • If the array has more columns than rows, the Lookup function will search for the desired value in the first column,
  • The values ​​in the Search Area must be sorted in ascending order.

We will process the data table below with sales achieved and rewards for sales milestones achieved. Calculate bonuses for employees who sell 15 products and sales for employees who get 59 products and 65 products.

Step 1:

In the reward result box for employees who sell 15 products, enter the formula =LOOKUP(15,B2:C6).

Lookup formula in array form

The reward results will be as shown below.

The results of the lookup function are in array form

Step 2:

Continue in the reward results box for employees who sell 59 products, enter the formula =LOOKUP(59,B2:C6).

Find zero values ​​in the range

The bonus result for employees who reach 59 products will still be 600,000. Even though the number 59 is not in the table, it will still have a return value.

The result is a zero value in the range

Step 3:

In the reward result box for the product is 65, we enter the formula =LOOKUP(65,B2:C6) then press Enter and the result will be 800,000 as shown.

Find the result with the larger value

If the user searches for reward results for those who reach 14 products, an error will be reported, because the value to be searched is smaller than the smallest value in the table.

Array Lookup error message

Notes when using the LOOKUP function in Excel

Use the LOOKUP function to look up a value in one column or row range, and retrieve the value from the same location in another column or row range. The LOOKUP function comes in two forms: vector and array.

The LOOKUP function accepts 3 arguments: lookup_value, lookup_vector, and result_vector. The first argument, lookup_value is the value to search for. The second argument, lookup_vector, is a row or column to search. LOOKUP assumes lookup_vector is sorted in ascending order. The third argument, result_vector is a row or column of results. Result_vector is optional.

When result_vector is provided. LOOKUP identifies a match in lookup_vector and returns the corresponding value from result_vector. If result_vector is not provided, LOOKUP returns the matching value found in lookup_vector.

LOOKUP has an effective way of handling specific problems, especially in searching for data. Therefore, you can be completely assured when using it.

See more:

Wishing you success!

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisment -
Google search engine

Most Popular

Recent Comments