How to use the LOOKUP function in excel?
To search for a certain value by row or by column in excel, we immediately think of using VLOOKUP or HLOOKUP. With the following article, we will learn more about how to use the new function, LOOKUP. The LOOKUP function is more advanced than the HLOOKUP and VLOOKUP functions. The LOOKUP function can be used when you need to look at a row or a column and find a value from the same position in the second row or column.
Lookup function has 2 forms: array form and vector form.
1. LOOKUP function in array form
Syntax: = LOOKUP (lookup_value, array).
Inside:
- lookup_value: The value to search.
- array: The data area containing the value to search.
Attention:
- Where the search value is not found in the array -> the function returns the largest value in the array but is smaller than the value to be searched.
- If look_value is smaller than the smallest value in array -> Lookup function returns error value.
- The values in the array must be sorted in ascending order for accurate results.
- If array has more columns than rows -> function finds in the first row.If the array has more squares or rows than columns, the function will look in the first column.
For example:
Sales of employees with a separate bonus.Find reward levels for employees who sell 30 products and employees who sell 55 products.
Calculate the sales of employees who sold 30 products.In a cell to calculate enter the formula: = LOOKUP (30, D6: D9).
Pressing Enter results the employee receives a reward of: 300,000
- For employees with sales of 55 products not included in the bonus column but still worth the return.
In the cell to calculate enter the formula: = LOOKUP (55, D5: E9).
Even salespeople who sold sales 55 received only 500,000 bonus
2. LOOKUP function in vector form
Syntax: = LOOKUP (lookup_value, lookup_vector, [result_vector]).
Inside:
- lookup_value: The value to search, is a required parameter.
- lookup_vector: The value container to search for but only contains one row or one column, the region data can be numeric, text or logical values, which are required parameters.
- result_vector: The search results container contains at most 1 row or 1 column, which is an arbitrary parameter.
Attention:
- Values in Lookup_vector must be arranged in ascending order so that the search process does not occur.
- Where the search value is not in the search area, the function takes the largest value in lookup_vector which is smaller than the value to be searched.
- If the search value is smaller than the smallest value in lookup_vector -> the function returns the error value.
For example:
You have an amount of money you want to buy a phone, you want to determine with which amount to buy the phone is most reasonable.
For example, with the amount of 3,500,000 you want to find out what kind of phone to buy.In the cell to find enter the formula: = LOOKUP (E5, E5: E9, D5: D9) or you can enter the formula: = LOOKUP (3500000, E5: E9, D5: D9).
Note that in this case you should not use commas separated by numbers
Press Enter -> With the amount of 3500000 you can choose the type of XIAOMI phone
The amount of 6,500,000 is not in column E, but the function still takes the largest value in column E but is less than 6,500,000
In a cell to calculate, enter the formula: = LOOKUP (6500000, E5: E9, D5: D9)
Press Enter -> for 6,500,000 you can buy an IPHONE phone
You should read it
- How to combine Vlookup function with Left function
- What is DNS and DNS Lookup?
- How to use VLOOKUP Function in Excel
- Function Address - The function returns the address of a cell in Excel (usage, examples, examples)
- How to use Visual Lookup object identification on iPhone
- Look up data in Excel tables: Replace VLOOKUP with INDEX and MATCH
- Save time with these text formatting functions in Microsoft Excel
- How to use the SUM function to calculate totals in Excel
May be interested
- DATE Function: Converts numbers to a valid date formatthe date function in excel is a useful and most commonly used function. let's learn how to use the date function in excel with tipsmake.com.com!
- How to use the LEN function in Excelthe len function in excel is used to measure the length of a certain string of characters, including spaces and correctly return the total character for the user.
- How to use COUNTIF function on Excelthe countif function on excel is the cell count function to satisfy the given condition, often used in statistics tables.
- How to use the MOD function and QUOTIENT function in Excelinjury in excel has many ways of doing it, can be used manually or using the calculation function.
- 8 little-known Excel functions that can save you a lot of workeven seasoned excel users often find themselves stuck performing tasks manually that could be automated with a few clever functions.
- How to use MID functions to get strings in Excelmid function in excel is a function that takes the middle character string corresponding to the value that the user requires to perform.
- How to use Excel's VALUE functionexcel's value function will convert a string to a digital form, which can be combined with other functions such as left function, right function, mid function.
- How to use the WRAPROWS function in Excelhaving trouble with handling large data sets in excel? then learn how to use the wraprows function to split them into more manageable rows.
- How to use the IFS function in Excel 2016the ifs function in excel is a logical function introduced in excel 2016. this function is a function that replaces the nested if function and is much easier to use. the ifs function checks one or more conditions and returns a value that meets the first true condition.
- How to use the NPER function in Excel to plan loans and savingsdo you want to effectively manage and control your personal finances? then we invite you to learn how to use excel's nper function.