ọdịnaya
Ụnyaahụ na marathon 30 Excel na-arụ ọrụ n'ime ụbọchị 30 we had fun with the function REPT (REPEAT) by creating charts inside a cell and using it for simple counting. It’s Monday, and once again it’s time for us to put on our thinker hats.
N'ụbọchị 16th nke marathon, anyị ga-amụ ọrụ ahụ LELIE ANYA (VIEW). This is a close friend VLOOKUP (VLOOKUP) na ECHICHE (GPR), but it works a little differently.
So, let’s study the theory and test the function in practice LELIE ANYA (VIEW). If you have additional information or examples on how to use this feature, please share them in the comments.
Function 16: LOOKUP
ọrụ LELIE ANYA (LOOKUP) returns a value from one row, one column, or from an array.
How can I use the LOOKUP function?
ọrụ LELIE ANYA (LOOKUP) returns the result, depending on the value you are looking for. With its help you will be able to:
- Find the last value in a column.
- Find the last month with negative sales.
- Convert student achievement from percentages to letter grades.
Syntax LOOKUP
ọrụ LELIE ANYA (LOOKUP) has two syntactic forms – vector and array. In vector form, the function looks for the value in the given column or row, and in array form, it looks for the value in the first row or column of the array.
The vector form has the following syntax:
LOOKUP(lookup_value,lookup_vector,result_vector)
ПРОСМОТР(искомое_значение;просматриваемый_вектор;вектор_результатов)
- ile anya (lookup_value) – Can be text, number, boolean, name, or link.
- lookup_vector (lookup_vector) – A range consisting of one row or one column.
- result_vector (result_vector) – a range consisting of one row or one column.
- argument ranges lookup_vector (lookup_vector) and result_vector (result_vector) must be the same size.
Ụdị nhazi ahụ nwere syntax ndị a:
LOOKUP(lookup_value,array)
ПРОСМОТР(искомое_значение;массив)
- ile anya (lookup_value) – Can be text, number, boolean, name, or link.
- the search is performed according to the dimension of the array:
- if the array has more columns than rows, then the search occurs in the first row;
- if the number of rows and columns is the same or there are more rows, then the search occurs in the first column.
- the function returns the last value from the found row/column.
Traps LOOKUP (VIEW)
- Na ọrụ LELIE ANYA (BROWSE) there is no option to search for an exact match, which is in VLOOKUP (VLOOKUP) and in ECHICHE (GPR). If there is no search value, then the function will return the maximum value not exceeding the search value.
- The array or vector being searched must be sorted in ascending order, otherwise the function may return an incorrect result.
- If the first value in the array/vector being looked up is greater than the lookup value, then the function will generate an error message #AT (#N/A).
Example 1: Finding the last value in a column
In the form of an array function LELIE ANYA (LOOKUP) can be used to find the last value in a column.
Excel help quotes value 9,99999999999999E + 307 as the largest number that can be written in a cell. In our formula, it will be set as the desired value. It is assumed that such a large number will not be found, so the function will return the last value in column D.
In this example, the numbers in column D are allowed not to be sorted, in addition, text values may come across.
=LOOKUP(9.99999999999999E+307,D:D)
=ПРОСМОТР(9,99999999999999E+307;D:D)
Example 2: Find the last month with a negative value
In this example, we will use the vector shape LELIE ANYA (VIEW). Column D contains the sales values, and column E contains the names of the months. In some months, things didn’t go well, and negative numbers appeared in cells with sales values.
To find the last month with a negative number, the formula with LELIE ANYA (LOOKUP) will check for each sales value that it is less than 0 (inequality in the formula). Next, we divide 1 on the result, we end up with either 1, or an error message #DIV/0 (#NKWUO/0).
Since the desired value is 2 is not found, the function will select the last found 1, and return the corresponding value from column E.
=LOOKUP(2,1/(D2:D8<0),E2:E8)
=ПРОСМОТР(2;1/(D2:D8<0);E2:E8)
Nkọwa: In this formula, instead of the argument lookup_vector (lookup_vector) expression substituted 1/(D2:D8<0), which forms an array in the computer's RAM, consisting of 1 and error values #DIV/0 (#NKWUO/0). 1 indicates that the corresponding cell in the range D2:D8 contains a value less than 0, and the error #DIV/0 (#DIV/0) – what is greater than or equal to 0. As a result, our task is to find the last 1 in the created virtual array, and based on this, return the name of the month from the range E2:E8.
Example 3: Converting student achievement from percentages to letter grades
Previously, we have already solved a similar problem using the function VLOOKUP (VPR). Today we will use the function LELIE ANYA (VIEW) in vector form to convert student achievement from percentages to letter grades. Unlike VLOOKUP (VLOOKUP) for a function LELIE ANYA (VIEW) It doesn't matter if the percentages are in the first column of the table. You can select absolutely any column.
In the following example, the scores are in column D, sorted in ascending order, and their corresponding letters are in column C, to the left of the column being searched.
=LOOKUP(C10,D4:D8,C4:C8)
=ПРОСМОТР(C10;D4:D8;C4:C8)
