Excel LOOKUP - Free Excel Tutorial

April 8, 2023April 8, 2023

Excel Array Construction

This article will talk about how to create one-dimensional array or two-dimensional array by using some functions in Excel. When using array formulas in Excel, we often use functions to construct arrays.

1. Generate Array with ROW or COLUMN Functions

Array formulas often need to use “natural number” as parameters of the function, such as the second parameter of the LARGE function, OFFSET function in addition to the first parameter. Through the manual way to enter a constant array will be more trouble, and easy to make mistakes. Then we can use the ROW or COLUMN function in EXCEL to generate a sequence, this method is very convenient and fast.

The following formula produces a vertical array of natural numbers from 1 to 10.

{=ROW (1:10)}

The following formula produces a horizontal array of natural numbers from 1 to 10.

{=COLUMN(A:J)}

2. Generating two-dimensional Array from one-dimensional Array

Below we will show you how to construct a new two-dimensional array with two columns of data.

a. One-dimensional range rearrangement to generate two-dimensional array

If there is a list of students and the name column of that list contains 10 students’ names, we need to randomly place the students’ names in the name column into the cell range of 5 rows and 2 columns (a new two-dimensional array).

We can use the following steps to randomize the names of students in column B to a 5 row 2 column range, such as the cell range E1:F5.

STEP1# Select the cell range E1:F5

STEP2# Enter the following array formula in the formula bar

=INDEX(B2:B11,RIGHT(SMALL(RANDBETWEEN(A2:A11^0,999)/1%+A2:A11,ROW(1:5)*2-{1,0}),2))

STEP3# Press CTRL + SHIFT +ENTER shortcut keys to convert the above formula into an array formula.

STEP4# You will see that the students’ names have been randomly placed in a two-dimensional range of array.

Let’s see how this array formula works.

=RANDBETWEEN(A2:A11^0,999)

The RANDBETWEEN function is used to generate an array of 10 values, where the elements are random integers between 1 and 1000. Since the elements are randomly generated, the size of the array elements is randomly ordered. The array formula generates an array of random integers as follows.

={484;203;468;525;702;220;13;163;386;54}

=RANDBETWEEN(A2:A11^0,999)/1%+A2:A11

The random integer array generated above is multiplied by 100 and then added to the ordinal array of 1 to 10. This ensures that the last two digits of the array elements are ordinal numbers 1 to 10.

={484;203;468;525;702;220;13;163;386;54}/1%+A2:A11

The above array formula returns the following array:

{48401;20302;46803;52504;70205;22006;1307;16308;38609;5410}

=ROW(1:5)*2-{1,0}

The ROW function generates a vertical array {1;2;3;4;5), and then subtracts a constant array {1,0} to produce a two-dimensional array of 5 rows and 2 columns.

=SMALL()

The result is taken as the second argument to the SMALL function, which sorts the array after multiplication and addition processing. Since the original size of the array is random, after sorting, the ordinal number corresponding to the last two digits of each element is randomly ordered.

=RIGHT()

The RIGHT function is used to extract the last two digits of each element, and the INDEX function is used to return the student’s name in the corresponding position in column B. In this way, the names in column B can be randomly populated into a two-dimensional array of 5 rows and 2 columns.

b. Combining two columns of data to create a two-dimensional array

We can use the VLOOKUP function to query from the right to the left, and we can use the array operation and IF function to swap two columns of data to generate a new two-dimensional array.

Here is an example of how the VLOOKUP function can be used to reverse the query by constructing a new array.

If you have a table of employee information, and you need to find the employee’s number by the employee’s name, you can use the VLOOKUP function in combination with the IF function to construct a two-dimensional array to find the corresponding employee number.

The employee information table is as follows:

To find the employee number by name, the steps are as follows.

STEP1# Select the cell B3

STEP2# Enter the following formula in the formula bar and press Enter

=VLOOKUP(B2,IF({1,0},E2:E11,D2:D11),2,)

STEP3# As you can see, Jerry’s employee number has been found.

Let’s see how the above formula works.

The core part of the formula is IF ({1,0},E2:E11,D2:D11), which uses a horizontal array {1,0} and two vertical arrays to perform operations to achieve the position of the column where the employee’s name and work number are swapped. Its returned memory arrays are：

{"Tom",1;"Jerry",2;"Jacey",3;"Wendy",4;"William",5;"Ocean",6;"scott",7;"charies",8;"ceila",9;"nicole",10}

The VLOOKUP function then queries the employee’s name in the two-dimensional array generated by the IF function and returns the corresponding employee number.

3. Extracting Sub-arrays From Data

In daily work, it is often necessary to extract part of the data from a column and reprocess it. For example, If you want to find out the list of employees who meet the specified requirements in the employee table.

The following describes how to extract some data from a column to form a subarray.

Suppose you have an employee salary table and you want to find out the names of employees whose salary is greater than $2000. The salary table is as follows.

You can refer to the following steps to obtain a list of employees who meet the requirements.

STEP1# First you need to select the cell range E2:E11

STEP2# Enter the following formula in the formula bar

=T(OFFSET(B1,SMALL(IF(C2:C11>2000,A2:A11),ROW(INDIRECT("1:"&COUNTIF(C2:C11,">2000")))),))

STEP3# Press CTRL + SHIFT +ENTER shortcut keys to convert the above formula into an array formula.

Let’s see how the above formula works.

=IF(C2:C11>2000,A2:A11)

First use the IF function to determine whether the salary meets the conditions, if the salary is greater than $2000, then return the employee’s ID, otherwise return the logical value FALSE.

=ROW(INDIRECT(“1:”&COUNTIF(C2:C11,”>2000″)))

The COUNTIF function is used to calculate the number of scores greater than 100 and is combined with the ROW function and INDIRECT function to generate a sequence of natural numbers from 1 to n.

=SMALL(IF(C2:C11>2000,A2:A11),ROW( INDIRECT(“1:”&COUNTIF(C2:C11,”>2000″))))

Use the SMALL function to find the employee number whose salary is greater than $2000 and return the following memory array.

={1;3;5;10}

=OFFSET(B1,{1;3;5;10},)

The OFFSET function extracts the employee’s name from the result returned by the SMALL function and returns the following array of employee names.

={"Tom";"Jacey";"William";"nicole"}

=T(OFFSET(B1,{1;3;5;10},))

Finally, the T function is used to convert the multi-dimensional reference returned by the OFFSET function into a memory array.

4. Extracting Sub-array from a two-dimensional Array

The cell range A1:C10 contains data of text and numeric type, see the figure below.

If you want to extract all the text-based data from the specified cell range A1:C10, then you can use the following array formula.

=T(INDIRECT(TEXT(SMALL(IF(A1:C10>="",ROW(A1:C10)/1%+COLUMN( A1:C10)),ROW(INDIRECT("1:"&COUNTIF(A1:C10,"*")))), "r0c00"),))

Let’s see how the above formula works.

=IF(A1:C10>=””,ROW(A1:C10)/1%+COLUMN( A1:C10))

The IF function is used to determine the type of data in the cell range. If the cell value is the text, then let the cell’s line number multiplied by 100, and then add the cell’s column number, and then return a numeric result; if the cell value is not text type, then return the logical value FALSE.

={FALSE,102,FALSE;FALSE,202,FALSE;FALSE,302,FALSE;FALSE,402,FALSE;FALSE,502,FALSE;FALSE,602,FALSE;FALSE,702,FALSE;FALSE,802,FALSE;FALSE,902,FALSE;FALSE,1002,FALSE}

=ROW(INDIRECT(“1:”&COUNTIF(A1:C10,”*”)))

The COUNTIF function is used to calculate the number of text values in the range of cells A1:C10, and combined with the ROW function and INDIRECT function to generate a series of natural numbers from 1 to n.

={1;2;3;4;5;6;7;8;9;10}

=SMALL(IF(A1:C10>=””,ROW(A1:C10)/1%+COLUMN( A1:C10)),ROW(INDIRECT(“1:”&COUNTIF(A1:C10,”*”))))

The SMALL function is used to extract the position information of the cell where the text is located and return a memory array.

={102;202;302;402;502;602;702;802;902;1002}

=INDIRECT(TEXT({102;202;302;402;502;602;702;802;902;1002}, “r0c00”),)

The TEXT function is used to convert the location information to R1C1 reference style, and then use the INDIRECT function to return to the cell reference.

=T(INDIRECT(TEXT({102;202;302;402;502;602;702;802;902;1002}, “r0c00”),))

Finally, the multi-dimensional references returned by the INDIRECT function are converted to memory arrays using the T function.

5. Fill the Merged Cells by Array Formula

In the merged cells, only the first cell has a value, while the rest of the cells are empty cells. When we work with the data, we may need to fill the empty cells in the merged cells with the corresponding values to meet the needs of the calculation.

The following is a product sales table, we need to fill the empty cells in the merged cells with the corresponding region name. The data table is as follows:

You can fill the data into the merged cells by using the following array formula.

=LOOKUP(ROW(A2:A13),ROW(A2:A13)/(A2:A13>""),A2:A13)

Let’s See How This Formula Works:

=ROW(A2:A13)/(A2:A13>””)

This formula assigns a non-empty cell in column A to the row number of that cell, and returns the error value #DIV/O! for empty cells, and finally returns a memory array.

{2;#DIV/0!;#DIV/0!;#DIV/0!;6;#DIV/0!;#DIV/0!;#DIV/0!;10;#DIV/0!;#DIV/0!;#DIV/0!}

Finally, the LOOKUP function is used to perform a fuzzy search and return the corresponding region name.

6. Convert Two-dimensional Array to one-dimensional Array

Some functions only support one-dimensional array as their arguments, not two-dimensional array. For example, the second argument of the MATCH function, the second argument of the LOOKUP function, and so on. If you want to complete the query in a two-dimensional array, you need to first convert the two-dimensional array to a one-dimensional array.

In the figure below, the cell range A1:C4 is a two-dimensional array, by using the following formula you can return the maximum value less than or equal to 100 in the cell range; LOOKUP function will perform a fuzzy search from a one-dimensional array returned by the SMALL formula, and return the value that matches the conditions.

The formula is as follows.

=LOOKUP(100,SMALL(A1:C4,ROW(1:12)))

Let’s see how this formula works:

=SMALL(A1:C4,ROW(1:12))

Because the cell range is 4 rows and 3 columns, it is a two-dimensional array containing 12 elements. You can generate a sequence of natural numbers from 1 to 12 by using the ROW function. Then use the SMALL function to sort the two-dimensional array and return a one-dimensional memory array. The result is as follows:

={16;60;78.6;79;97;97;99;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}

The LOOKUP function performs a fuzzy lookup by row and ignores the error value #NUM!. Finally, the maximum value less than or equal to 100 is returned, which is 99.

March 22, 2023March 22, 2023

How to Find the Last or First Value in a Range Greater Than X in Excel

This post will guide you how to find the first value greater than a specified value in a range of cells in Excel. How do I find the last value greater that a given value in a row with a formula in Excel.

Assuming that you have a list of data in range B1:B6, in which contain numeric values. And you want to find the first number from the list that is greater than a given number (30). Or you wish to find the last one number from your data that is greater than a number 30. The below will show you one formula to accomplish it.

1. Find the Last Value Greater Than X Using Formula

If you want to identify the last value in your range B1:B6 that is greater that number 30, you can use the following array formula based on the LOOKUP function and the IF function. Like this:

=LOOKUP(9.999999999E+307,IF(B1:B6>30,B1:B6))

You can type the above formula into a blank cell in your worksheet, and make sure to press Ctrl +shift +Enter keys on your keyboard to change your formula as array formula.

2. Find the Last Value Greater Than X with VBA Code

You can also use a user-defined function (vba code) to find the last value greater than a given value in Excel. You just need to do the following steps:

Step1: Open your Excel workbook and press Alt + F11 to open the Visual Basic Editor.

Step2: In the editor, click on “Insert” and select “Module” from the drop-down menu.

Step3: In the new module, enter the following code, and save the module and return to the Excel worksheet.

find last value greater thatn x vba1.png

Function LastGreaterThanbyExcelhow(R As Range, Val As Variant) As Variant
    Dim i As Integer
    LastGreaterThanbyExcelhow = ""
    For i = R.Cells.Count To 1 Step -1
        If R.Cells(i).Value > Val Then
            LastGreaterThanbyExcelhow = R.Cells(i).Value
            Exit Function
        End If
    Next i
End Function

Step4: In a blank cell, enter the formula:

=LastGreaterThanbyExcelhow(A1:A6, 30)

Where A1:A6 is the range of cells you want to search for the last value greater than the given value and “30” is the given value.

Step5: Press Enter to calculate the function and display the result.

find last value greater thatn x vba2.png

Note: The function will return an empty string (“”) if no value is found greater than the given value.

find last value greater thatn x vba3.png

3. Find the First Value Greater Than X Using Formula

If you want to find the first value in a range that is greater that number 30, you can use the following formula based on the INDEX function and the MATCH function to identify the first value in the range B1:B6 greater that number 30. Like this:

=INDEX(B1:B6,MATCH(TRUE,INDEX(B1:B6>30,0),))

You just need to type the above formula into a blank cell in your worksheet, and make sure to press Ctrl +shift +Enter keys on your keyboard to change your formula as array formula.

4. Find the First Value Greater Than X with VBA Code

You can also use a user-defined function in Excel to find the first value greater than a given value in a range of cells. Here is the VBA Code:

find first value greater thatn x vba1.png

Function FindFirstGreaterThanExcelhow(TargetValue As Double, RangeToSearch As Range) As Double
    Dim cell As Range
    For Each cell In RangeToSearch
        If cell.Value > TargetValue Then
            FindFirstGreaterThanExcelhow = cell.Value
            Exit Function
        End If
    Next cell
   FindFirstGreaterThanExcelhow = CVErr(xlErrValue)
End Function

In a cell, enter the formula:

 =FindFirstGreaterThanExcelhow(30, A1:A6)

Where 30 is the value you want to search for and A1:A6 is the range of cells to search.

Press Enter to execute the formula and the function will return the first value greater than the target value in the specified range.

find first value greater thatn x vba2.png

5. Video: Find the Last or First Value in a Range Greater Than X in Excel

This video will demonstrate how to find the last or first value in a range greater than X in Excel using a formula or a User Defined Function (UDF).

6. Related Functions

Excel LOOKUP function
The Excel LOOKUP function will search a value in a vector or array.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
Excel MATCH function
The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])….

January 25, 2022October 22, 2022

Extract Unique Items From A List

This post will guide you how to extract unique itmes from a given list in Microsoft Excel. How to create a newly formula to get unique values from a range cells in Excel. The unique list of items is the first one found, which does not have a value equal to any other in the array. You can use one formula based on the INDEX function, the MATCH function in combination with the COUNTIF function to extract unique values. The generic formula is as follow:

=INDEX(range, MATCH(0, COUNTIF(unique using, range),0))

Explanation:

This formula will extract unique values from an array or range based on their position rather than just blank cells. First, you need to select the items for your unique list and place them in a range. Then you need to create a new formula in the next blank cell in your worksheet that states, “=INDEX(range, MATCH(0, COUNTIF(unique using, range),0))".

This formula will extract the unique values for just one column. If you prefer to have them in a different column, just drag the formula to that cell. You can also highlight the newly created formula and drag it down throughout your spreadsheet, but be sure to copy the values instead of cutting them before you do so since once you cut or delete them, they are gone.

Lets understand with an example:

"=INDEX($A$1:$A$8,MATCH(0,COUNTIF($C$1:C1,$A$1:$A$8),0))"

Extract Unique Items From A List In Excel1

The data in this table consists of the range cells $A$1:$A$8.

To create a formula that automatically sums up all of the values in an array, simply enter Control+Shift+Enter.

Clarification:

This formula starts with a basic index.

=INDEX(range,row)

This is telling Excel to find the row in your list of data where you want to start extracting unique items.

The hard work is figuring out which row number will give us unique values to identify which record needs more attention easily. This process starts with MATCH and COUNTIF; the main trick here is:

COUNTIF($C$1:C1,range)

With COUNTIF, we can see how many times an item already in our unique list appears on the master list. We use an expanding reference range ($C$1:C1) for this calculation, which means that it will return all values up until but not including $C$.

A growing reference is absolute on one side but relative when copied down. In this case, as the formula gets pasted into each new row of unique data in a list or table structure, it will continue expanding its reach until all available fields have been added with corresponding reference pointing.

To avoid creating a circular reference, we start one row C1 above, where the first unique entry is found. This means we want to count items *already* within that particular list and not just include the current cell. So instead, we begin on the highest able column available at the start.

All the headings should be unique, so they don’t show up in your master list too!

The criteria in COUNTIF returns an array when given multiple values. Therefore, each new row contains different results like this:

{0;0;0;0;0;0;0;0} // row 2

{1;0;0;1;0;0;0;0}// row 3

{1;1;0;1;0;1;1;0}// row 4

As you can see, the MATCH function is able to identify which value in its array is zero. This means we can match each unique entry with a corresponding single-digit number and get all the desired data within our index formula.

The arrays we created in countif can be used to find positions (row numbers). For this, we use MATCH and set it up with an exact match. When put together our two sets produce a result like this:

MATCH(0,{ 0;0;0;0;0;0;0;0},0) // 1 (excel)

MATCH(0,{ 1;0;0;1;0;0;0;0},0) // 2 (word)

MATCH(0,{ 1;1;0;1;0;1;1;0},0) // 3 (access)

The MATCH locator counts items by looking for a count of zero (i.e., when there are no duplicates). This works because it always returns the first match, regardless if others have been found before yours.

INDEX is the key to finding what you’re looking for. It’s like a search engine that returns information by number rather than a name. With this simple difference in methodology comes huge benefits when quickly indexing large volumes of data.

Non-Array Version With LOOKUP

If you are using Excel 2021, you don’t have the INDEX function, but you do have LOOKUP. Lookup can accomplish most of what index can do, except it does it in a linear fashion. It is slow and requires you to enter one value at a time or copy/paste from an adjacent cell range to build your lookup table.

With this formula, you’ll be able to extract all unique items from any list in Excel:

=LOOKUP(2,1/(COUNTIF($C$1:C1, $A$1:$A$8)=0), $A$1:$A$8)

The array operation can be done natively in LOOKUP by using a similar formula as INDEX MATCH.

Using this, you can simply fill in your list of unique items by using lookup’s maximum row parameter.
The COUNTIF function returns each value that occurs in the list. The result of COUNTIF is put in a Boolean array [0;1], which yields the count from each item.
Then divide each number by its own array, creating an even more precise error count.
When applied as an input to the LOOKUP function, this vector becomes the lookup table that stores all possible values for you!
The average value of 2 in this lookup_vector is larger than any other value.
LOOKUP always finds the last non-error value in an array.
When a value is found in LOOKUP, it returns that value. The range cells will contain all values returned by lookup when there is more than one occurrence of an input variable.”

This blog post discussed how to extract unique items from a list in Excel. We hope these tips will save you time and make it easy for you to get the most important data. If there are any other tricks or shortcuts we can provide, please let us know!

Related Functions

Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
Excel MATCH function
The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])….
Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
Excel LOOKUP function
The Excel LOOKUP function will search a value in a vector or array.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…

June 3, 2021June 4, 2021

Average the Last N Numeric Values in Excel

AVERAGE function is one of the most popular functions in Excel. Apply AVERAGE together with some other functions, we can calculate average simply for some complex situations.

In this article, we will introduce you to calculate average of the last N numbers from a range contains both numeric values and non-numeric values.

EXAMPLE

Average the Last N Numeric Values 7

In this case, we want to calculate the average of the last 3 numeric values in “Numbers” column. As non-numeric value “ABC” exists in last three cells, so we cannot directly apply AVERAGE(A9:A11) directly. To calculate average ignoring invalid values, we need the help of other functions.

In this article, to approach our goal, except the main AVERAGE function, we also apply ROW, ISNUMBER, IF, LARGE, LOOKUP functions.

SOLUTION

To create a formula to get average in this case, we need to know:

1) Distinguish numbers and non-numeric values from range “Numbers”. Ignore non-numeric values in calculation.

2) Find out the last three cells with numbers in “Numbers”.

3) Find out last three values through searching for the last three positions.

FORMULA with AVERAGE & OTHER FUNCTIONS

In C2, we input the formula

=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(Numbers),ROW(Numbers)),{1,2,3}), ROW(Numbers), Numbers)).

After typing, press Enter, average of the last three numbers is 12.

Average the Last N Numeric Values 7

In “Numbers” list, the last three values are 20, 15 and 1, the average is (20+15+1)/3=12, so the formula returned value is correct. The last three values are found out properly.

FUNCTION INTRODUCTION

The main function in this formula is “AVERAGE”, it can return the average of last three values; Others are supported to find out numbers (ignoring non-numeric values), mark row numbers for the last three numbers, and the through searching row numbers to return corresponding numbers.

1. ROW function returns row number for a given range reference.

Syntax:

=ROW(reference)

Example. ROW(A1:A3) returns row numbers of range A1:A3, so we get {1;2;3}.

Average the Last N Numeric Values 7

2. ISNUMBER function returns True (for numeric values) or False (for strings, errors) based on value is a numeric value or not. For blank cells, it returns False.

Syntax:

=ISNUMBER(value)

Example. You can input a value, a cell reference, or a range reference for “value”.

Average the Last N Numeric Values 7

3. IF function returns “true value” or “false value” based on the result of provided logical comparison. It is one of the most popular function in Excel.

Syntax:

=IF(logical_test,[value_if_true],[value_if_false])

Example, =IF(ISNUMBER(E1:E3),”Yes”,”No”).

Average the Last N Numeric Values 7

4. LARGE function returns the Kth largest number from a given set of numbers or a range reference.

Syntax:

=LARGE(array,k)

Example, =LARGE(E1:E3,{1;2}), an array {3;2} is returned. If you set k=2, only 2 is returned.

Average the Last N Numeric Values 7

5. LOOKUP function can through searching for a row or column to return the corresponding value in the same position but in the second row or column. It has two different syntaxes, in this case, it is for vector:

Syntax:

=LOOKUP(lookup_value, lookup_vector, [result_vector])

Example, =LOOKUP(H2,E2:E3,F2:F3), apply lookup to find out name for id1.

Average the Last N Numeric Values 7

6. AVERAGE function returns the average of numbers from a given range reference.

Syntax:

=AVERAGE(number1, [number2], …)

Example. For arguments “number1, number2,…”, they can be a set of numbers, or an array of numbers like {1;2;3}.

Average the Last N Numeric Values 7

FORMULA EXPLANATION

=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(Numbers),ROW(Numbers)),{1,2,3}), ROW(Numbers), Numbers))

This formula contains 6 functions, we explain functions from inside to outside.

1. For ISNUMBER(Numbers), “Numbers” is A2:A11, so ISNUMBER function check values in each cell in this range and returns “True” for numeric values and “False” for non-numeric values or blank. ISNUMBER(Numbers) returns below array:

{TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE}

2. ROW(Numbers)

 returns row numbers for range A2:A11.

{2;3;4;5;6;7;8;9;10;11}

3. With the help of ISNUMBER and ROW functions, IF function returns row numbers for cells with numeric values.

IF({TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE},{2;3;4;5;6;7;8;9;10;11})

Based on logical test result, IF returns row number for “true value” and keeps “False” for “false value” as [value_if_false] is omitted. Then we get below array from IF function:

{2;3;FALSE;5;6;FALSE;FALSE;FALSE;10;11}

4. Now above array returned from IF function participates into LARGE function calculation.

LARGE({2;3;FALSE;5;6;FALSE;FALSE;FALSE;10;11},{1,2,3})

K is an array {1,2,3}, so LARGE function returns the largest three values from array {2;3;FALSE;5;6;FALSE;FALSE;FALSE;10;11}. In this step, LARGE function returns the last three row numbers for cells with numbers.

{11,10,6}

5. In step#4, we get the last three row numbers properly. Then we can apply LOOKUP function to lookup corresponding values in row 11, row 10 and row 6 from range “Numbers”.

Row(Numbers) is applied twice in this formula, the first one is “value_if_true” for IF function, the second one is used as “lookup_vector” for LARGE function.

LOOKUP({11,10,6}, ROW(Numbers), Numbers)=LOOKUP({11,10,6}, {2;3;4;5;6;7;8;9;10;11}, {5;10;0;8;20;#NAME?;"ab$cd";"ABC";15;1})

In this step, LOOKUP function returns proper values from A2:A11 after searching for row numbers 11, 10 and 6. So we get below array at last:

{1,15,20}

6. After above all steps, we get =AVERAGE({1,15,20}). So, the returned value is (1+15+20)/3=12.

Related Functions

Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value],
Excel ISNUMBER function
The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
Excel LOOKUP function
The Excel LOOKUP function will search a value in a vector or array.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
Excel LARGE function
The Excel LARGE function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array. The syntax of the LARGE function is as below:= LARGE (array,nth)…

September 21, 2020

How to Find the First or Last Positive or Negative Number in a Column/List in Excel

Suppose we have a list of data entry, we want to look up the first positive number among them, is there any way to find it out? And if we want to look up the first or last negative number, how can we do? Actually, to find out the first or last positive/negative number from a list of data, we can apply formula with some functions like INDEX, LOOKUP, MATCH to get them properly. This free tutorial will show you the formulas to help you find out positive or negative number per your request.

Precondition:

See screenshot below. There are some positive and negative numbers in a column.

Method 1: Find the First Positive Number by Formula

Step 1: Select a blank cell, enter the formula =INDEX(A1:A10,MATCH(TRUE,A1:A10>0,0)).

Step 2: As it is an array formula, so press Ctrl+Shift+Enter to get result. Verify that the first positive number 123 is displayed properly.

Method 2: Find the First Negative Number by Formula

Step 1: Select a blank cell, enter the formula =INDEX(A1:A10,MATCH(TRUE,A1:A10<0,0)). Just change > in method1 formula to <.

Step 2: As it is an array formula, so press Ctrl+Shift+Enter to get result. Verify that the first negative number -91 is displayed properly.

Method 3: Find the Last Positive Number by Formula

Step 1: Select a blank cell, enter the formula =LOOKUP(2,1/(A1:A10>0),A1:A10).

Step 2: As it is an array formula, so press Ctrl+Shift+Enter to get result. Verify that the last positive number 78 is displayed properly.

Method 4: Find the Last Negative Number by Formula

Step 1: Select a blank cell, enter the formula =LOOKUP(2,1/(A1:A10<0),A1:A10). Compare to get the positive number by formula, just change > in the formula to <.

Step 2: As it is an array formula, so press Ctrl+Shift+Enter to get result. Verify that the last negative number -101 is displayed properly.

Related Functions

Excel LOOKUP function
The Excel LOOKUP function will search a value in a vector or array.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
Excel MATCH function
The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])….

January 11, 2019

How to Find the First or Last Non-blank Cell in a Row or Column in Excel

This post will guide you how to find the first non-blank cell in a row or column or a range in Excel. How do I get the value of the last non-blank cell in a row or column using formula in Excel.

Get First Non-blank Cell Value
Get Last Non-blank Cell Value

Get First Non-blank Cell Value

Assuming that you have a list of data in range A1:A5, and you want to find the first non-blank cell in a given range of cells in Excel. How to do it. You can use a formula based on the INDEX function and the MATCH function to get the first non-blank value in a one-column range in Excel. Here is the formula you can use:

=INDEX(A1:A5,MATCH(TRUE,INDEX((A1:A5<>0),0),0))

Type this formula into a blank cell and press Enter key on your keyboard. You would get the first non-blank cell value from your given range A1:A5.

Or you can use an array formula based on the INDEX function, the MATCH function and the ISBLANK function to achieve the same result of extracting the first non-blank cell value from a given range in Excel. Like this:

=INDEX(A1:A5,MATCH(FALSE,ISBLANK(A1:A5),0))

Type this formula into a blank cell and press Ctrl + Shift + Enter keys on your keyboard to make your formula as array formula.

Get Last Non-blank Cell Value

If you want to find the value of the last non-blank cell in a row or column in Excel, you can use a formula based on the LOOKUP function to achieve it. Here is the formula we are using is:

=LOOKUP(2,1/(A1:A5<>""),A1:A5)

Type this formula into a blank cell and press Enter key on your keyboard. You would get the last non-blank cell value from your given range A1:A5.

Related Functions

Excel LOOKUP function
The Excel LOOKUP function will search a value in a vector or array.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
Excel MATCH function
The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])….
Excel ISBLANK function
The Excel ISBLANK function returns TRUE if the value is blank or null.The syntax of the ISBLANK function is as below:= ISBLANK (value)…

November 21, 2018November 21, 2018

How to VLOOKUP to Return Value if Date Falls between Two dates in Excel

This post will guide you how to do a VLOOKUP between two dates and return corresponding value in Excel. How do I VLOOKUP between two dates and return corresponding value with lookup formula in Excel. How to lookup a value if the date fall between two dates in Excel.

VLOOKUP Value If Date Falls between Two Dates
Video: VLOOKUP to Return Value if Date Falls Between Two Dates

VLOOKUP Value If Date Falls between Two Dates

Assuming that you have a list of data in Range A1:C4, which contain start date, end date, and Sales product, and you want to lookup a given date (2019/4/3), if this date falls between the start dates and end dates, and then return its corresponding product name. How to achieve it. You can use a formula based on the LOOKUP function and the DATE function to achieve the result of doing vlookup operation to lookup a date and return value.

Like this formula:

=LOOKUP(2,1/($A$2:$A$4<=DATE(2019,4,3))/($B$2:$B$4>=DATE(2019,4,3)),$C$2:$C$4)

Type this formula into a blank cell, such as: D1, and then press Enter key in your keyboard. And the value will be returned in Cell D1.

You just need to change the date number as you need in DATE function to do the VLOOKUP operation.

Video: VLOOKUP to Return Value if Date Falls Between Two Dates

Related Functions

Excel LOOKUP function
The Excel LOOKUP function will search a value in a vector or array.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
Excel DATE function
The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…

September 5, 2018

Copy and Paste Only Non-blank Cells

This post will guide you how to copy and paste only non-blank cells in Excel 2013/2016. How do I copy only non blank cells with VBA code in Excel. Or how to get non-blank cells from a range of cells with a formula in Excel.

This post will introduce four methods to copy and paste only non-blank cells in a selected range in Excel.

Method1: Copy non-blank cells with Filter feature

If you want only copy non-blank cells in a range in Excel, you need to select the non-blank cells firstly, then press Ctrl +C keys to copy the selected cells. So how to only select all non-blank cells in the selected range in your worksheet. Just do the following steps:

#1 select the range of cells that you want to filter all non-blank cells.

#2 go to DATA tab, click Filter command under Sort & Filter group.

#3 the drop-down button should appear in the first cell of your range. Click on this drop down button. de-select the (Blanks) option and then click on the OK button.

#4 you will see that only non-blank cells are displayed in the selected range.

#5 then you can select all non-blank cells in the current range and press Ctrl + C shortcuts in your keyboard, and then press Ctrl + V keys to paste the selected cells into a destination cell.

Method2: Copy non-blank Cells with Go To Special Feature

You can also use the Go To Special feature to select all non-blank cells in the selected range of cells. Do the following steps:

#1 select the range of cells that you want to filter all non-blank cells.

#2 go to HOME tab, click Find & Select command under Editing group. And select Go To Special from the popup menu list. The Go To Special window will open.

#3 select Constants radio button in the Select section under Go To Special dialog. Then click OK button.

#4 all non-blank cells in the selected range are highlighted.

#5 you can press Ctrl + C shortcuts to copy cells have been highlighted, and then press Ctrl + V keys to paste the selected cells into a destination cell.

Method3: Copy non-blank Cells with VBA Macro

You can also use an Excel array formula based on the LOOKUP function, the CHOOSE function, the INDEX function, the SMALL function, the IF function, the ROW function and the ROWS function to extract all non-blank cells from a cell. Like this:

=LOOKUP("xxxxx",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF($A$1:$A$9<>"",ROW($A$1:$A$9)),ROWS($B$1:B1)))))

Type this formula into cell B1, and then press Ctrl +Shift +Enter shortcuts to change this formula as array formula. And then drag the AutoFill Handle from Cell B1 to B9.

All non-blank cells from the range A1:A9 are extracted in the range B1:B9.

Method4: Copy non-blank Cells with Formula

You can also write an Excel VBA macro to copy and paste all non-blank cells, just do the following steps:

#1 select the range of cells that you want to filter all non-blank cells

#2 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#3 then the “Visual Basic Editor” window will appear.

#4 click “Insert” ->”Module” to create a new module.

#5 paste the below VBA code into the code window. Then clicking “Save” button.

Sub CopyPasteNonBlankCells()
    Application.Selection.SpecialCells(xlCellTypeConstants).Copy Destination:=Range("B1")
End Sub

#6 back to the current worksheet, then run the above excel macro. Click Run button.

#7 let’s see the result.

Related Functions

Excel LOOKUP function
The Excel LOOKUP function will search a value in a vector or array.The LOOKUP function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
Excel Choose Function
The Excel CHOOSE function returns a value from a list of values. The CHOOSE function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the CHOOSE function is as below:=CHOOSE (index_num, value1,[value2],…)…
Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
Excel ROWS function
The Excel ROWS function returns the number of rows in a cell reference.The syntax of the ROWS function is as below:= ROWS(array)…
Excel SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….

August 20, 2018August 20, 2018

List all Worksheet Names

This post will guide you how to get a list of all worksheet names in an excel workbook. How do I List the Sheet names with Formula in Excel. How to generate a list of all sheet tab names using Excel VBA Code.

Assuming that you have a workbook that has hundreds of worksheets and you want to get a list of all the worksheet names in the current workbook. And the below will introduce 3 methods with you.

Get All Worksheet Names Manually

If there are only few worksheets in your workbook, and you can get a list of all worksheet tab names by manually. Let’s see the below steps:

#1 open your workbook

#2 double click on the sheet’s name in the sheet tab. Press Ctrl + C shortcuts in your keyboard to copy the selected sheet.

#3 create a notepad file, and then press Ctrl +V to paste the sheet name.

#4 follow the above steps 2-3 to copy&paste all worksheet names into notepad file.

Get All Worksheet Names with Formula

You can also use a formula to get a list of all worksheet names with a formula. You can create a formula based on the LOOKUP function, the CHOOSE function, the INDEX function, the MID function, the FIND function and the ROWS function. Just do the following steps:

#1 go to FORMULAS tab, click Name Manager command under Defined Names group. The Name Manager dialog will open.

#2 click New… button to create a define name, type Sheets in the Name text box, and type the formula into the Refers to text box.

=GET.WORKBOOK(1)&T(NOW())

#3 Type the following formula into a blank cell and press Enter key in your keyboard, and then drag the autofill handle over others cells to get the rest sheet names.

=LOOKUP("xxxxx",CHOOSE({1,2},"",INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROWS(A$1:A1))))

You will see that all sheet names have been listed in the cells.

Get All Sheet Names with Excel VBA Macro

You can also use an Excel VBA Macro to quickly get a list of all worksheet tab names in your workbook. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Sub GetListOfAllSheets()
   Dim w As Worksheet
   Dim i As Integer
   i = 1
   Sheets("Sheet1").Range("A:A").Clear
   For Each w In Worksheets
       Sheets("Sheet1").Cells(i, 1) = w.Name
       i = i + 1
   Next w
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

#6 Let’s see the result.

Related Functions

Excel MID function
The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
Excel LOOKUP function
The Excel LOOKUP function will search a value in a vector or array.The LOOKUP function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
Excel Choose Function
The Excel CHOOSE function returns a value from a list of values. The CHOOSE function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the CHOOSE function is as below:=CHOOSE (index_num, value1,[value2],…)…
Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
Excel ROWS function
The Excel ROWS function returns the number of rows in a cell reference.The syntax of the ROWS function is as below:= ROWS(array)…
Excel FIND function
The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])

August 14, 2018August 14, 2018

Convert Weekday Name to Number

This post will guide you how to convert weekday string to number in excel. How do I convert weekday name to number with a formula in excel. How to convert day of week to number in excel. Is there an easy way to convert a weekday string which is either “Monday” or “Tuesday” into a number in excel.

Convert Weekday Name to Number

Assuming that you have list of data B1:B7 that contain weekday names, you want to convert all weekday names to numbers, how to achieve it.

You can use a formula based on the MATCH function to achieve the result. Just like this formula:

=MATCH(B1,{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0)

Type this formula into the formula box of one blank cell, such as: Cell C1, then press Enter key in your keyboard, and drag the AutoFill Handler over other cells to apply this formula to convert weekday string to numbers.

You will see all weekday names in range B1:B7 have been converted to numbers in range C1:C7.

Or you can also use another complex formula based on the LOOKUP function to convert weekday names to numbers in excel. Like this:

=LOOKUP(A1,{"Monday",1;"Friday",5;"Saturday",6;"Sunday",7;"Thursday",4;"Tues  day",2;"Wednesday",3})

Related Functions

Excel LOOKUP function
The Excel LOOKUP function will search a value in a vector or array.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
Excel MATCH function
The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])….

July 11, 2018

Finding the Max and Min value in an Alphanumeric Data

This post will guide you how to find the maximal and minimal value or string from an alphanumeric data list in excel. How do I retrieve or get the max or min string value based on alphabetic order from a string data in excel.

For example, if you want to find the Maximal or minimal string value from an alphanumeric data list in the range B1:B5, you can create a formula based on the LOOKUP function and the COUNTIF function.

To get the max value based on the alphabetic order, you can use the following formula:

=LOOKUP(2,1/(COUNTIF(B1:B5,">"& B1:B5)=0), B1:B5)

You can also use the following array formula to achieve the same result. Just follow these steps to apply the array formula:

#1 type the formula in the formula box of cell D1

=INDEX(B1:B5,MATCH(MAX(COUNTIF(B1:B5,”<“& B1:B5)),COUNTIF(B1:B5,”<“& B1:B5),0))

#2 Press Ctrl + Shift +Enter on your keyboard to make the formula as an array formula.

#3 the formula will be change as the following style:

{=INDEX(B1:B5,MATCH(MAX(COUNTIF(B1:B5,"<"& B1:B5)),COUNTIF(B1:B5,"<"& B1:B5),0))}

#4 let’s see the result.

To get the min value based on the alphabetic order, you can use the following formula:

=LOOKUP(2,1/(COUNTIF(B1:B5,"<"&B1:B5)=0),B1:B5)

Of course, you can also use another array formula to get the minimal value based on alphabetic order, just refer to the above steps to apply the following array formula.

{=INDEX(B1:B5,MATCH(MAX(COUNTIF(B1:B5,">"& B1:B5)),COUNTIF(B1:B5,">"& B1:B5),0))}

Related Functions

Excel LOOKUP function
The Excel LOOKUP function will search a value in a vector or array.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
Excel MATCH function
The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])….
Excel MAX function
The Excel MAX function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.= MAX(num1,[num2,…numn])…

December 24, 2017December 26, 2017

How to get last match that cell contains one of several values in a range

This post explains that how to check a cell that contains one of several values in another range, and then return the last match value. And we talked that how to test a cell to see that if it contains one of several values in a list in the previous post. And this post will guide you how to get the last match value in the range.

Get Last Match that cell contains one of several values

If you want to check a cell that if it contains one of several values in a range, you can use the SEARCH function to search find_text in a range inside a text string. Then we can use the LOOKUP function to get the last match values.

For example, you have a list of text strings in the range B1:B4 and you need to check each cell if it contains one of several values in the range D2:D4, if TRUE, then return the last match values from the range D2:D4. You can write down the following formula:

=LOOKUP(2,1/SEARCH($D$2:$D$4,B1), $D$2:$D$4)

Let’s see how this formula works:

= SEARCH($D$2:$D$4,B1)

This formula will search each value from the range D2:D4 inside within_text in Cell B1, if it find the matched string, then return the position of the first character in Cell B1, if not, it returns the #VALUE error. So this formula will return an array result like this:

{1;7;12}

=1/SEARCH($D$2:$D$4,B1)

The array result returned by the SEARCH function is divided by number 1, then this formula returns another new array result that is composed of the #VALUE! Error and decimal values. If the item is a decimal value, then it indicates that one value found. The array is like this:

{1;0.142857142857143;0.0833333333333333}

The returned array result goes into the LOOKUP function as its lookup_vector argument.

=LOOKUP(2,1/SEARCH($D$2:$D$4,B1), $D$2:$D$4)

You can see that the value of lookup vector will never larger than 1, and the lookup value is set to 2. So the lookup value is not able to be found. So the LOOKUP function will match the last numeric value in the array result of the lookup_vector, then return the value from the same position in the range D2:D4.

Related Formulas

Check If Cell Contains All Values from Range
If you want to check if a cell contains all values in a list, you can use a combination of the SEARCH function, the SUMPRODUCT function, the ISNUMBER function and COUNTA function…
Get first match that cell contains one of several values in a range
You can use a combination of the INDEX function, the MATCH function, the ISNUMBER function and the SEARCH function to create a new excel array formula to get first match cell contains…
Check if Cell contains one of many values from range
Assuming that you have a list of text strings in the range B1:B3 and you want to check each text string if it contains one of several values in a range E1:E3. If it contains any of text string in range E1:E3, then it should be return TRUE value, otherwise, it should return FALSE….

Related Functions

Excel LOOKUP function
The Excel LOOKUP function will search a value in a vector or array.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
Excel SEARCH function
The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH (find_text, within_text,[start_num])…

December 8, 2017December 7, 2017

How to Lookup the Value with Multiple Criteria

This post explains that how to lookup the value in a table or a range based on the multiple criteria in excel. This post will guide you how to search for a specified value with multiple criteria using INDEX and Match functions. And how to use Lookup function to lookup the value with multiple criteria. How to use the SUMPRODUCT function to lookup the value with multiple criteria in excel.

Using INDEX and MATCH functions
Using LOOKUP function
Using SUMPRODUCT function

Using INDEX and MATCH functions

If you want to lookup the value with multiple criteria in a range, you can use a combination with the INDEX function and MATCH function to create an array formula.

For example, if you want to search for the bonus values that matches the two criteria, the member name is equal to “jenny” and the month value is equal to “Sep”. You can write down the following array formula using INDEX function and MATCH function:

=INDEX(D2:D10,MATCH(1,(B2:B10="jenny")*(C2:C10="Sep"),0))

Let’s see how the above formula works:

=(B2:B10=”jenny”)

The above formula will check each item in the range B2:B10 if it is equal to the string “jenny”, if so, returns TRUE. If not, returns FALSE. The returned result is an array like this:

{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}

The position of the “TRUE” value in the above array result is actually the position of the lookup value in the range B2:B10.

=(C2:C10=”Sep”)

The above formula returns an array result like this:

{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

It will check each item in the range C2:C10 if it is equal to the string “Sep”. If so, it returns TRUE, or returns FALSE. From the position of the TRUE value in the array result, you can get the position of the lookup value that matched the criteria in the range C2:C10.

=(B2:B10=”jenny”)*(C2:C10=”Sep”)

This formula will convert the TRUE FALSE values to 1s and 0s, and then perform multiplication operation to return the below array result. The position of item “1s” in the array is the position of lookup values matching multiple criteria in the range D2:D10. So you can use the MATCH function to search for 1 value to get the position result.

{0;1;0;0;0;0;0;0;0}

{=MATCH(1,(B2:B10=”jenny”)*(C2:C10=”Sep”),0)}

The MATCH function returns the position number of the lookup bonus value that matching two criteria. It returns 2.

=INDEX(D2:D10,MATCH(1,(B2:B10=”jenny”)*(C2:C10=”Sep”),0))

The INDEX function returns the bonus value at a position returned by the MATCH function. It returns $150.

Using LOOKUP function

You can also use the LOOKUP function to search for the value with multiple criteria, just refer to the following formula to lookup bonus values that matches two criteria (member name =”jenny” and Month value =”Sep”):

=LOOKUP(2,1/(B2:B10="jenny")/(C2:C10="Sep"),(D2:D10))

Let’s see how this formula works:

=(B2:B10=”jenny”)/(C2:C10=”Sep”)

This formula perform a division operation to get an array result like this:

{0;1;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}

=1/(B2:B10=”jenny”)/(C2:C10=”Sep”)

This formula divides 1 by the above array result to get the following new array like this:

{#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}

The position of item 1 in the array is the position of lookup bonus value matching criteria.

=LOOKUP(2,1/(B2:B10=”jenny”)/(C2:C10=”Sep”),(D2:D10))

The LOOKUP function looks up 2 in the above array, matches the nearest smaller value (1), and returns the bonus value from the range D2:D10 that is in the same row.

Using SUMPRODUCT function

There is another way to search for the value with multiple criteria in excel, using SUMPRODUCT function. You can try to use the following SUMPRODUCT to achieve the same result.

=SUMPRODUCT((B2:B10="jenny")*(C2:C10="Sep")*(D2:D10))

Let’s see how this formula works:

=(B2:B10=”jenny”)*(C2:C10=”Sep”)*(D2:D10)

This formula multiples two array results and values in range D2:D10 together and returns another array result like this:

{0;150;0;0;0;0;0;0;0}

=SUMPRODUCT((B2:B10=”jenny”)*(C2:C10=”Sep”)*(D2:D10))

The SUMPRODUCT function multiples the items in the arrays and returns sum of the results. So it returns 150.

Related Formulas

Reverse a List or Range
If you want to reverse a list or range, you can use a combination of the INDEX function, the COUNTA function, the ROW function or ROWS function to create a new formula. you can use the following formula:=INDEX($A$2:$A$5,COUNTA($A$2:$A$5)-ROWS($C$2:C2)+1)…
Transpose Values Based on the Multiple Lookup Criteria
If you want to lookup the value with multiple criteria, and then transpose the last results, you can use the INDEX function with the MATCH function to create a new formula.…

Related Functions

Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
Excel MATCH function
The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])….
Excel LOOKUP function
The Excel LOOKUP function will search a value in a vector or array.The LOOKUP function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])….
Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products. And it returns a numeric value.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)….

December 1, 2017December 11, 2017

How to get the position of Last Occurrence of a character or string in a cell

In the previous post, we have talked that how to get the position of the last occurrence of a specific value in a single range or column. And this post will guide you how to get the position of the last occurrence of a character or text string in a Cell using below two ways in excel.

Get the position of Last Occurrence of a character Using Excel Formula
Get the position of Last Occurrence of a character using user defined Function

Get the position of Last Occurrence of a character Using Excel Formula

If you want to get the position of the last occurrence of a character in a cell, then you can use a combination of the LOOKUP function, the MID function, the ROW function, the INDIRECT function and the LEN function to create an excel formula. For example: Assuming that there is a text string in Cell B1, and you can use the following formula to get the last occurrence of the character “-” in Cell B1:

=LOOKUP(2,1/(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)="-"),ROW(INDIRECT("1:"&LEN(B1))))

Let’s see how the above formula works:

=ROW(INDIRECT(“1:”&LEN(B1)))

The ROW function will return the row number based on the row references returned by the INDIRECT function. so it will return the below array:

{1;2;3;4;5;6;7;8;9}

=MID(B1,ROW(INDIRECT(“1:”&LEN(B1))),1)

I have talked the above MID function in the previous post to split text string into an array in a cell. So the above formula returns an array like as below:

{"e";"-";"x";"-";"c";"-";"e";"-";"l"}

=MID(B1,ROW(INDIRECT(“1:”&LEN(B1))),1)=”-“

The above formula will check each element in array returned by the above MID function if it is equal to the dash character (“-“). If so, returns TRUE, otherwise, returns FALSE. As it is an array formula, so the above formula should also return an array like as below:

{FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}

=1/(MID(B1,ROW(INDIRECT(“1:”&LEN(B1))),1)=”-“)

The above formula use 1 to divide each element in the array returned by the MID function. If the value of element in array is TRUE, then returns 1, otherwise, returns a #DIV/0! Error. Just like the below:

{#DIV/0!;1;#DIV/0!;1;#DIV/0!;1;#DIV/0!;1;#DIV/0!}

=LOOKUP(2,1/(MID(B1,ROW(INDIRECT(“1:”&LEN(B1))),1)=”-“),ROW(INDIRECT(“1:”&LEN(B1))))

The LOOKUP function searches for the value of 2 in the first array returned by “1/MID()” formula, you should know that the lookup function can’t find the value of 2 in the first array, so it uses the largest value in the first array that is less than or equal to 2.then returns a value from the same position in the second array returned by the second ROW function.

So the result returned by the above LOOKUP function is the position of the last occurrence of the dash character in Cell B1. It is 8.

You can also use another formula to achieve the same result as follows:

=SEARCH("#",SUBSTITUTE(B1,"-","#",LEN(B1)-LEN(SUBSTITUTE(B1,"-",""))))

Let’s see how the above formula works:

=SUBSTITUTE(B1,”-“,””)

The SUBSTITUTE function will replace all dash characters (“-“) of Cell B1 with empty string.

=LEN(B1)-LEN(SUBSTITUTE(B1,”-“,””))

The above formula returns the numbers of dash character in the Cell B1.

=SUBSTITUTE(B1,”-“,”#”,LEN(B1)-LEN(SUBSTITUTE(B1,”-“,””)))

The first SUBSTITUTE function will replace the last occurrence of the dash character with the hash character in Cell B1.

=SEARCH(“#”,SUBSTITUTE(B1,”-“,”#”,LEN(B1)-LEN(SUBSTITUTE(B1,”-“,””))))

The SEARCH function searches the hash character in a text string returned by the first SUBSTITUTE function, and then returns the position of the hash character. It should be the last occurrence of the dash character in Cell B1.

Get the position of Last Occurrence of a character using user defined Function

You can also create a new user defined function to get the position of the last occurrence of a specific character or string in Excel VBA:

1# click on “Visual Basic” command under DEVELOPER Tab.

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module named as: getPosition

4# paste the below VBA code into the code window. Then clicking “Save” button.

Function getPosition1(strVal As String, strChar As String) As Long
    getPosition1 = InStrRev(strVal, strChar)
End Function

5# back to the current workbook, then enter the below formula in Cell C1:

= getPosition1(B1,"-")

Related Formulas

Get the position of Last Occurrence of a value in a column
If you want to find the position number of the last occurrence of a specific value in a column (a single range), you can use an array formula with a combination of the MAX function, IF function, ROW function and INDEX Function.
Get the Position of the nth Occurrence of a Character in a Cell
If you want to get the position of the nth occurrence of a character using a excel formula, you can use the FIND function in combination with the SUBSTITUTE function.
Combine Text from Two or More Cells into One Cell
If you want to combine text from multiple cells into one cell and you can use the Ampersand (&) symbol.If you are using the excel 2016, then you can use a new function TEXTJOIN function to combine text from multiple cells…
Split Text String to an Array
If you want to convert a text string into an array that split each character in text as an element, you can use an excel formula to achieve this result. the below will guide you how to use a combination of the MID function, the ROW function, the INDIRECT function and the LEN function to split a string…
Find the Relative Position in a Range or Table
If you want to know the relative row position for all rows in an Excel Range (B3:D6), you can use a excel Array formula as follows:=ROW(B3:D6)- ROW(B3) + 1. You can also use another excel array formula to get the same result as follows:=ROW(B3:D6)-ROW(INDEX(B3:D6,1,1))+1…
Get the First Row Number in a Range
If the ROW function use a Range as its argument, it only returns the first row number.You can also use the ROW function within the MIN function to get the first row number in a range. You can also use the INDEX function to get the reference of the first row in a range, then combined to the ROW function to get the first row number of a range.…
Get the Last Row Number in a Range
If you want to get the last row number in a range, you need to know the first row number and the total rows number of a range, then perform the addition operation, then subtract 1, the last result is the last row number for that range.…

Related Functions

Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
Excel MID function
The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
Excel INDIRECT function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
Excel LEN function
The Excel LEN function returns the length of a text string (the number of characters in a text string).The LEN function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEN function is as below:= LEN(text)…
Excel LOOKUP function
The Excel LOOKUP function will search a value in a vector or array.The LOOKUP function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
Excel Substitute function
The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string. The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE (text, old_text, new_text,[instance_num])….
Excel SEARCH function
The Excel SEARCH function returns the number of the starting location of a sub-string in a text string. The syntax of the SEARCH function is as below:= SEARCH (find_text, within_text,[start_num])….

July 21, 2017September 5, 2018

Excel Lookup Function

This post will guide you how to use Excel LOOKUP function with syntax and examples in Microsoft excel.

Description

The Excel LOOKUP function will search a value in a vector or array.

The LOOKUP function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.

The LOOKUP function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the LOOKUP function is as below:

= LOOKUP (lookup_value, lookup_vector, [result_vector])

Where the LOOKUP function arguments are:
Lookup_value -This is a required argument. A value that you want to search in the lookup_vector.
Lookup_vector – This is a required argument. A excel Range that only contains one row or one column.
Result_vector – This is an optional argument. A excel Range that only contains one row or one column. It must be the same size as Lookup_vector. The lookup function will look up the value in the lookup_value range and returns the value from the same position in the result_vector.
Note: if Result_vector is omitted, the Lookup function will return the first column data.

Example

The below examples will show you how to use Excel LOOKUP Lookup and Reference Function to search a value from a vector or array.

#1 To look up the value “34”in A1:A2 range, then get the value from the same row or column in B1:B2 range, just using the following excel formula: =LOOKUP(“34”,A1:A2,B1:B2)

More Excel LOOKUP Formula Examples

Finding the Max and Min value in an Alphanumeric Data
if you want to find the Maximal or minimal string value from an alphanumeric data list in the range B1:B5, you can create a formula based on the LOOKUP function and the COUNTIF function.….
Convert Weekday Name to Number
Assuming that you have list of data B1:B7 that contain weekday names, you want to convert all weekday names to numbers.You can use a formula based on the MATCH function to achieve the result
List all Worksheet Names
Assuming that you have a workbook that has hundreds of worksheets and you want to get a list of all the worksheet names in the current workbook. And the below will introduce 3 methods with you..…
Copy and Paste Only Non-blank Cells
If you want only copy non-blank cells in a range in Excel, you need to select the non-blank cells firstly, then press Ctrl +C keys to copy the selected cells. So how to only select all non-blank cells in the selected range in your worksheet..…