In Excel basic functions, Lookup function is often used by many people. Lookup is a reference function, a search function in Excel. This function will be used when you need to see a row or a column, looking for a value in the same position or column.
Lookup function will be used in two forms, vector and array. Each form will have a formula as well as a different application case. The following article will guide you how to use the Lookup function in Excel.
Instructions for using Lookup function on Excel
The Vector form Lookup function is used to find a value in a range of 1 row or 1 column, and returns the value from the same position in the second range of 1 row or 1 column. This vector form will be used when you want to determine the range that contains the values you want to compare, or when the range to be searched for is a range of values or values can be changed.
Array form to look up the value specified in the first column or row of the array, then return the value from the same position in the last column or row of the array. Array form used when the search range is low, the value remains the same and must be sorted.
1. Use the Lookup function in the Vector format
The formula is = Lookup (The value to look for, The area to find the value to look for, The container of the result value) .
The value to look for can be a number, a text, a logical value, a name, or a reference to a value.
The area to be searched for is text, number or logic value. The values in this area must be sorted in ascending order so that no errors will occur.
The area containing the result value can be 1 row or 1 column.
Note:
We will apply to the statistics table below. For example, if you want to buy a phone for about 7,500,000, what kind of phone will you find? And if you want to find a phone in about 13 million, look for one.
Step 1:
In the result input box for the phone in about 7,500,000 users enter the formula as = LOOKUP (7500000, C2: C6, B2: B6) and press Enter. If using numbers, do not use unit separators.
The result will be the Lenovo phone.
Step 2:
To find the phone line in about 13 million, enter the formula in the result box = LOOKUP (13000000, C2: C6, B2: B6) and press Enter.
The value of 13000000 is not in the data area, so Lookup will look for a value less than 13000000.
As a result we will have an iPhone phone company that should buy it for about 13 million.
If you need to find a phone within 5 million VND, you will report # N / A error as shown below. So worth 5,000,000 less than the smallest value in the table is 6,000,000 and report an error. The result of the value you need to find will be in the range of 6,000,000 to 12,000,000 only.
2. Use the array Lookup function
The syntax is = LOOKUP (Search value, Search area) .
The value to look for is the Lookup function value to look for in an array.
The search area is the range of cells that contain text, numbers, and logical values to look for.
Note:
We will process the data table below with the sales achieved and reward the sales mark. Calculating bonus for employees selling 15 products and sales for employees is 59 products, 65 products.
Step 1:
In the results box for the 15 product sales staff, enter the formula = LOOKUP (15, B2: C6) .
The reward results will look like the picture below.
Step 2:
Continue in the results box for employees to sell 59 products, enter the formula = LOOKUP (59, B2: C6) .
The reward results for employees reaching 59 products will still be 600,000. Although number 59 is not in the table, it will still have a return value.
Step 3:
In the reward box for the product is 65, we enter the formula = LOOKUP (65, B2: C6) and press Enter and the result will give 800,000 as shown.
If a user finds a reward for a 14-person product, an error is reported, because the value to be searched is less than the smallest value in the table.
Here's how to use the detailed Lookup function in Excel. Lookup will be divided into two types, vector and array. The formula and usage of these two forms are not the same.
See more:
I wish you all success!
ncG1vNJzZmismaXArq3KnmWcp51ktbDDjK2mZq2jmnqtu86krKlllqq7pMDIqKVmoZ5isrmvxKU%3D