How to Transpose Values Based on the Multiple Lookup Criteria

This post explains that how to transpose values from columns to rows based on the multiple lookup criteria in excel.

In the previous, we talked that how to transpose values from columns to rows using Paste Special Transpose, it’s just rearrange all data in a range and do not apply for any criteria.

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.

For example, to transpose the values in both column B and Column C based on the multiple criteria: member’s name is equal to the range B2:B10, and month’s value is equal to the range C2:C10, then extract the bonus value from the range D2:D10. you can use the following array formula:

=INDEX($D$2:$D$10,MATCH(1,($B$2:$B$10=$F4)*($C$2:$C$10=G$3),0))

Let’s see how the above formula works:

=($B$2:$B$10=$F4)

The above formula will check if each value in the range B2:B10 is equal to the value in Cell F4, if so, return TRUE, otherwise, returns FALSE. So the above formula returns an array result like this:

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

=($C$2:$C$10=G$3)

The above formula will check if each month value in the range C2:C10 is equal to the value in Cell G3, if so, return TRUE, otherwise, returns FALSE. So the above formula returns an array result like this:

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

=($B$2:$B$10=$F4)*($C$2:$C$10=G$3)

the above formula returns an array result like this:

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

= MATCH(1,($B$2:$B$10=$F4)*($C$2:$C$10=G$3),0)

The MATCH function used the above array result(containing one and zero) to find the position of item “1”, it is actually the position of the bonus value that matched the multiple criteria.

transpose value with multiple criteria1

=INDEX($D$2:$D$10,MATCH(1,($B$2:$B$10=$F4)*($C$2:$C$10=G$3),0))

The INDEX function extracts the value based on the position result returned by the above MATCH function. So it returns “$100”in Cell G4.

transpose value with multiple criteria2

You can drag the Fill Handler in the Cell G4 to other cells to fill up the entire range G4:I6 as you need.

transpose value with multiple criteria2

2. Video: Transpose Values Based on the Multiple Lookup Criteria in Excel

This video will show you how to use an array formula to transpose values based on multiple lookup criteria in Excel.

3. 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)…
  • Lookup the Value with Multiple Criteria
    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…

4. 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])….

How to Concatenate Text based on unique values in Another Column in Excel

This post will guide you how to concatenate text values based on unique values in another column in Excel. How do I concatenate cells based on specific criteria in Excel.

1. Concatenate Text Based on unique Values in Another Column

Assuming that you have a list of data in range A1:B6, in which contain product IDs and product Names. And you want to concatenate product names based on unique ID values (There are duplicated ID values in Column A), How to do it. You need to extract unique product IDs in another range, and then concatenating text values based on newly created range with a User Defined Function. Here are the steps:

Step1: you can use an Excel Array formula based on the IFERROR function, the INDEX function, the MATCH function and the COUNTIF function to extract the unique product ID values.

=IFERROR(INDEX($A$2:$A$6, MATCH(0,COUNTIF($C$1:C1, $A$2:$A$6), 0)),"")

Type this formula into cell C2, and press Ctrl + Shift + Enter keys on your keyboard to change it as array formula.  And then drag the AutoFill Handle down to other cells until getting blank cells.

concatenate text based on special criteria1

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

Get the position of the nth using excel vba1

Step3: then the “Visual Basic Editor” window will appear.

Step4: click “Insert” ->”Module” to create a new module.

convert column number to letter3

Step5: paste the below VBA code (code from here) into the code window. Then clicking “Save” button.

concatenate text based on special criteria2
Function Combinerows(CriteriaRng As Range, Criteria As Variant, _
ConcatenateRng As Range, Optional Delimeter As String = " , ") As Variant
    Dim i As Long
    Dim strResult As String
    On Error GoTo ErrHandler
    If CriteriaRng.Count <> ConcatenateRng.Count Then
        Combinerows = CVErr(xlErrRef)
        Exit Function
    End If

    For i = 1 To CriteriaRng.Count
       If CriteriaRng.Cells(i).Value = Criteria Then
           strResult = strResult & Delimeter & ConcatenateRng.Cells(i).Value
       End If
       Next i

       If strResult <> "" Then
           strResult = Mid(strResult, Len(Delimeter) + 1)
       End If

     Combinerows = strResult
     Exit Function
     ErrHandler:
     Combinerows = CVErr(xlErrValue)
End Function

Step6: back to the current worksheet, then type the following formula in a blank cell, and then press Enter key.

=combinerows(A2:A6,C2,B2:B6)

Step7: drag the AutoFill handle over other cells to  concatenate text based on unique product ID values.

concatenate text based on special criteria3

2. Video: Concatenate Text based on unique values in Another Column

This video will show how to use a formula in combination with a User defined function with VBA code to concatenate text based on unique values in another column in Excel.

3. Related Functions

  • Excel IFERROR function
    The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)…
  • 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 syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….

How to Find the Largest Value in a Column and Return the Adjacent Cell Value in Excel

This post will guide you how to find the largest value in a column and then return the adjacent cell value with a formula in Excel. How do I find the max value and return the cell value from the left adjacent column in Excel.

1. Find the Largest Value and Return the Adjacent Cell Value

Assuming that you have a list of data in range A1:B5, and you want to find the largest value from the Sales column and then return the adjacent cell value in product column. How to do it. You can use a formula based on the VLOOKUP function and the MAX function to achieve the result. Like this:

=VLOOKUP(MAX(A2:A5), A2:B5, 2, FALSE)

Type this formula into a blank cell and press Enter key on your keyboard.

find largest value return adjacent cell value1

You should see that the adjacent cell value is extracted.

The A2:B5 is the data range that you want to use in the VLOOKUP function.  And the Number 2 is the column number that the matched value is returned, it should be the product column number.

2. Find the Max Value and Return Value from Left Adjacent Cell

If you want to find the max value in Sales column and return value from the left adjacent cell in your worksheet, you need to use the INDEX function to create a formula in combination with the MATCH function and the MAX function. Like this:

=INDEX(A2:A5,MATCH(MAX(B2:B5),B2:B5,0))

Type this formula into a blank cell and press Enter key on your keyboard.

find largest value return adjacent cell value2

3. Video: Find the Largest Value in a Column and Return the Adjacent Cell Value in Excel

This video will demonstrate how to use a formula to find the largest value in a column and return the adjacent cell value in Excel.

4. 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 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])…
  • Excel VLOOKUP function
    The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….

Convert State Names To Abbreviations

Assume that you got a task to convert the full state’s name into the abbreviations in MS Excel, and for doing this task, you might choose to do it manually, which is an acceptable way only if you don’t have any other way to do it!

But fortunately, there is a way to convert a state’s name into abbreviations, and after reading this article carefully, you would get to know about that way by which you can do this task in a few seconds.

So let’s get straight into it.

abbr state names1

1. Convert State Names To Abbreviations

a. General Formula

The Formula to convert state names to the abbreviations in a matter of seconds is mentioned as follows:

=VLOOKUP(States_Name,states_Data,2,0)

b. Explanation of Syntax

Before getting knowledge about how to use this Formula to get the work done, we first need to understand how each syntax of the Formula contributes to making this Formula worthful:

  • VLOOKUP: The VLOOKUP function retrieves data from a range or table row by row.
  • Parenthesis (): The primary function of this symbol is to group the elements.

Let’s See How This Formula Works

A simple formula based on VLOOKUP may be used to convert complete state names to their two-letter abbreviation. The Formula in B2 in the example is:

=VLOOKUP(A2, states_Data,2,0)
abbr state names1

Where “ states_Data ” refers to the designated E2:F7 range:

This means that this method is based on a database with columns for the full state name and the 2-letter abbreviation. Because we’re using VLOOKUP, we need the whole name in the first column. The table has been dubbed “states_Data” for clarity’s sake.

VLOOKUP is set to retrieve the lookup value from column A. The table array represents the designated range “ states_Data,” The column index is 2 (to extract the abbreviation from the second column). The final option, range lookup, has been set to zero (FALSE).

VLOOKUP finds the matching entry in the “states_Data” database and returns the 2-letter abbreviation.

2. Convert Abbreviations to State Names (Lookup In Reverse)

What if you have an abbreviation of the states and want to search up the complete state name in the sample lookup table? You’ll need to use INDEX and MATCH instead in such a situation. With a lookup value in A2, this Formula will yield the whole state name as shown in the lookup table:

=INDEX(D2:D7,MATCH(A2,E2:E7,0))
abbr state names1

You may use this version to convert a 2-letter abbreviation to a complete state name if you wish to utilize the same-named range “ states_Data.”

=INDEX(INDEX(states_Data,0,1),MATCH(A1,INDEX(states_Data,0,2),0))
abbr state names1

By specifying a row number of zero, we may utilize INDEX to return whole columns. This is an interesting and helpful aspect of the INDEX function: if you specify zero for the row, you receive complete column.

You may convert abbreviations to complete state names using the INDEX and MATCH methods. The INDEX and MATCH functions can help you out to retrieve the complete state names depending on the abbreviations.

3. Video: Convert State Names To Abbreviations

This video will show you how to convert state names to abbreviations in Excel using a formula.

4. Related Functions

  • Excel VLOOKUP function
    The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….
  • 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])….

How To Align Duplicate Values within Two Columns in Excel

This post will guide you how to align duplicate values within two columns based on the first column in your worksheet in Excel. How do I use an formula to align two columns duplicate values in Excel.

1. Aligning Duplicate Values in Two Columns using Formula

Assuming that you have two columns including product names in your worksheet. and you want to find duplicate values in those two columns(Column A and Column B) and align them based on the first column in a new column(Column A and Column B). How to do it. And you can use an formula based on the IF function, the ISNA function, the MATCH function, and the INDEX function. Like below:

=IF(ISNA(MATCH(A1,$B$1:$B$7,0)),"",INDEX($A$1:$B$7,MATCH(A1,$B$1:$B$7,0),2))

Type this formula into the first cell in Column C, and press Enter key on your keyboard to apply it. then drag the AutoFill handle down to other cells to apply this formula.

How to align duplicate values within two columns1

Note: Cell A1 is the first cell in the first column, and B1:B7 is the range of cells in the second column.

2. Aligning Duplicate Values in Two Columns with VBA Code

You can also align duplicate values within two columns using VBA Macro in Excel, just do the following steps:

Step1: Press “Alt” and “F11” keys at the same time to open the Visual Basic Editor.

Step2: In the Visual Basic Editor, select “Insert” from the menu bar, then choose “Module” to create a new module.

Step3: Copy and paste the below VBA code into the new module. Close the Visual Basic Editor and return to the Excel sheet.

How To Align Duplicate Values within Two Columns in Excel vba1.png
Sub CheckDuplicateValues_ExcelHow()
    Dim sourceRange As Range, lookupRange As Range, outputRange As Range
    Dim i As Long, matchIndex As Variant
    
    ' prompt the user to select the source range
    On Error Resume Next ' to handle the cancel button
    Set sourceRange = Application.InputBox("Select the source range:", Type:=8)
    On Error GoTo 0 ' reset the error handling
    
    If sourceRange Is Nothing Then Exit Sub ' exit if the user cancels
    
    ' prompt the user to select the lookup range
    On Error Resume Next
    Set lookupRange = Application.InputBox("Select the lookup range:", Type:=8)
    On Error GoTo 0
    
    If lookupRange Is Nothing Then Exit Sub
    
    ' prompt the user to select the output range
    On Error Resume Next
    Set outputRange = Application.InputBox("Select the output range:", Type:=8)
    On Error GoTo 0
    
    If outputRange Is Nothing Then Exit Sub
    
    For i = 1 To sourceRange.Cells.Count
        matchIndex = Application.Match(sourceRange.Cells(i), lookupRange, 0)
        If IsError(matchIndex) Then
            outputRange.Cells(i).Value = ""
        Else
            outputRange.Cells(i).Value = Application.Index(sourceRange.Resize(, 2), matchIndex, 2)
        End If
    Next i
End Sub

Step4: Press “Alt” and “F8” keys at the same time to open the Macro dialog box. Select the macro ” CheckDuplicateValues_ExcelHow” from the list and click the “Run” button to execute the code.

How To Align Duplicate Values within Two Columns in Excel vba2.png

Step5: The code will prompt you to select the source range, lookup range, and output range.

How To Align Duplicate Values within Two Columns in Excel vba3.png
How To Align Duplicate Values within Two Columns in Excel vba4.png
How To Align Duplicate Values within Two Columns in Excel vba5.png

Step6: The code will run and populate the output range with the values that match between the source and lookup ranges.

How To Align Duplicate Values within Two Columns in Excel vba6.png

You should now have the results in the output range.

3. Video: Aligning Duplicate Values in Two Columns

This video will show you how to align duplicate values within two columns in Excel using both a formula and a VBA code.

4. Related Functions

  • 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])….
  • Excel ISNA function
    The Excel ISNA function used to check if a cell contains the #N/A error, if so, returns TRUE; otherwise, the ISNA function returns FALSE.The syntax of the ISNA function is as below:=ISNA(value)….

How to Look Up the Lowest Value in A List by VLOOKUP/INDEX/MATCH Functions in Excel

VLOOKUP function is very useful in our daily work and we can use it to look up match value in a range, then get proper returned value (the returned value may be just adjacent to the match value). Sometimes we only want to look up the lowest value among all matched values in the list and get its adjacent value, how can we do?

This tutorial will help you to look up the lowest value in a list by VLOOKUP function and User Defined Function with VBA code. However, except VLOOKUP function, we can also use INDEX/MATCH functions together in some situations to look up the lowest value as well. Please see details below.

Precondition:

Prepare a table consists of name, score and range columns. Now we want to get the ‘Range’ of the lowest score.

Look Up the Lowest Value in A List by VLOOKUPINDEXMATCH 1

1. Look Up the Lowest Value by VLOOKUP Function

Step1: In E2, enter the formula:

=VLOOKUP(MIN(B2:B6),B2:C6,2,FALSE)

As we want to find the ‘Range’ of the lowest score, we need to look up the lowest score among all score first, so we enter MIN(B2:B6) as lookup_value.

Look Up the Lowest Value in A List by VLOOKUPINDEXMATCH 2

Step2: Click Enter to get returned value in C column.

Look Up the Lowest Value in A List by VLOOKUPINDEXMATCH 3

Comment:

a.If there are two duplicate lowest values, VLOOKUP function will look up the first match value and return its adjacent value.

Look Up the Lowest Value in A List by VLOOKUPINDEXMATCH 4

b.This method only works well when returned value is listed in the right column.

2. Look Up the Lowest Value using User Defined Function with VBA Code

You can also create a User Defined function with VBA Code to lookup the lowest value in a list to replace the above VLOOKUP formula. Just do the following steps:

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

Adding Comma Character at End of Cells vba1.png

Step2: In the Visual Basic Editor, click Insert > Module to insert a new module.

Adding Comma Character at End of Cells vba1.png

Step3: Copy the below VBA code for the custom function and paste it into the new module. Save the VBA module by clicking File > Save or by pressing Ctrl + S.

How to Look Up the Lowest Value in A List by VLOOKUPINDEXMATCH Functions in Excel vba 1.png
Function MyVLOOKUP_Excelhow(rng As Range, lookup_value As Variant, column_index As Integer, exact_match As Boolean) As Variant
    Dim lookup_range As Range
    Dim result As Variant
    Set lookup_range = rng
    lookup_value = Application.WorksheetFunction.Min(lookup_range.Columns(1))
    result = Application.WorksheetFunction.VLookup(lookup_value, lookup_range, column_index, exact_match)
    MyVLOOKUP_Excelhow = result
End Function

Step4: In your Excel worksheet, enter a formula using the custom function just like you would any other built-in Excel function. Enter the following formula in Cell E2:

=MyVLOOKUP_Excelhow(B2:C6,0,2,FALSE)

Step5: Press Enter to calculate the result of the formula using the custom function.

How to Look Up the Lowest Value in A List by VLOOKUPINDEXMATCH Functions in Excel vba 2.png

3. Look Up the Lowest Value by INDEX/MATCH Functions

In above example, if we want to know the name of the lowest score, VLOOKUP function doesn’t work. So we use INDEX and MATCH functions combination to look up the lowest value here.

Step1: In B2, enter the formula:

=INDEX(A2:A6,MATCH(MIN(B2:B6),B2:B6,0))

MATCH function returns the location of cell, in this example it returns the lowest value’s row number. Then we can use INDEX function to get proper Name from A2:A6 refer to row number.

Look Up the Lowest Value in A List by VLOOKUPINDEXMATCH 5

Step2: Click Enter to get returned value.

Look Up the Lowest Value in A List by VLOOKUPINDEXMATCH 6

Comment:

a. If there are two duplicate lowest values, INDEX/MATCH functions will look up the first match value and return its adjacent value.

Look Up the Lowest Value in A List by VLOOKUPINDEXMATCH 7

b. This method works well for that returned value lists on the both sides of match value.

4. Video: Look Up the Lowest Value in A List

This video will demonstrate how to use the VLOOKUP function and VBA code to look up the lowest value in a list in Excel.

5. 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 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])…
  • Excel MIN function
    The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….
  • Excel VLOOKUP function
    The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….
  • 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])….

How to Lookup the Next Largest Value

This post will provide you with two different methods to lookup the next largest value in Excel: using a formula and using VBA code. While both methods achieve the same result, they differ in their implementation and can be used depending on your specific requirements.

1. Find the Next Largest Value Using Formula

If you want to get the next largest value in another column, you can use a combination of the INDEX function and the MATCH function to create an excel formula.

For example, if we used the VLOOKUP function to lookup the value 200, then it returns “103”, so it returns the value that is less than or equal to the lookup value. but if you want to return the next largest value in the range A2:A5, you can use the following formula:

=INDEX(A2:A5,MATCH(200,A2:A5)+1)

And if you want to find the next largest value in another column B2:B5, you just need to change the first argument of the INDEX function, use the range B2:B5 instead of A2:A5, the formula is as follwos:

=INDEX(B2:B5,MATCH(200,A2:A5)+1)

Let’s see how the above formula works:

=MATCH(200,A2:A5)+1

lookup next largest value 1

The MATCH function return the position of the lookup value 200 in the range A2:A5, if the MATCH function is not find the exact match, then it would return an approximate matched value(it should be less than or equalt to the lookup value) by default, so it returns 1.

To get the position of the next largest value, the position number returned by the match function need to add 1 again. so now we got the position of the next largest value in the range A2:A5. It returns 2.

=INDEX(A2:A5,MATCH(200,A2:A5)+1)

lookup next largest value2

=INDEX(B2:B5,MATCH(200,A2:A5)+1)

lookup next largest value3

The INDEX function returns the value based on the given position that returned by the above MATCH function.

so if you used the Range A2:A5, then the above INDEX function returns the next largest value in the range A2:A5.

And if you used the Range B2:B5, then it returns the next largest value in the range B2:B5.

You can also use the following array formula to achieve the same result.

=INDEX(B2:B5,MATCH(TRUE,A2:A5>200,0))
lookup next largest value4

2. Find the Next Largest Value with VBA Code (User Defined Function)

You can also create a User defined function with VBA code to find the next largest value in a given range of cells in Excel. Just do the following steps:

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

Adding Comma Character at End of Cells vba1.png

Step2: In the Editor, go to “Insert” > “Module” to create a new module.

Adding Comma Character at End of Cells vba1.png

Step3: Copy and paste the following code into the new module:

How to Lookup the Next Largest Value 10.png
Function FindNextLargest_ExcelHow(range1 As Range, range2 As Range, lookup_value As Double) As Variant
    Dim match_index As Variant
    Dim result As Variant
    Dim i As Long
    
    On Error Resume Next
    match_index = WorksheetFunction.Match(lookup_value, range1, 0)
    On Error GoTo 0
    
    If IsError(match_index) Then
        FindNextLargest_ExcelHow = CVErr(xlErrNA)
    Else
        For i = match_index + 1 To range1.Cells.Count
            If range1.Cells(i).Value > lookup_value Then
                result = range2.Cells(i).Value
                Exit For
            End If
        Next i
        
        If IsEmpty(result) Then
            FindNextLargest_ExcelHow = CVErr(xlErrNA)
        Else
            FindNextLargest_ExcelHow = result
        End If
    End If
End Function

Step4: Save the module by going to “File” > “Save” and then close the Editor.

Step5: In your worksheet, select the cell where you want to display the next largest value. Type the following formula into the formula bar:

=FindNextLargest(A2:A5,B2:B5,200)

Step6: this formula should return “word”, which is the next largest value in column A after 200.

How to Lookup the Next Largest Value 11.png

3. Video: Find the Next Largest Value

This video will demonstrate how to lookup the next largest value using both a formula and VBA code.

4. Related Formulas

  • 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…
  • 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)…

5. 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 VLOOKUP function
    The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….

How to get nth Match with One Criteria using INDEX/MATCH

This post will guide you how to get nth match based on one criteria with INDEX and MATCH in Excel. In the previous post, we used one excel formula to get the position of the nth occurrence of a value based on one criteria.

1. Get nth Match with One Criteria using INDEX/MATCH Formula

At this moment, we can also use it in the new formula to extract the nth match value using INDEX function. Like the below array formula:

{=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth))}

For example, if you want to find the 2th occurrence of the member “jenny” in the range B2:B10 and extracts its relative bonus value in the range D2:D10, you can used the following array formula:

=INDEX(D2:D10, SMALL(IF(B2:B10="jenny", ROW(B2:B10)-ROW(INDEX(B2:B10,1,1))+1),2))

Let’s see how this formula works

= SMALL(IF(B2:B10=”jenny”, ROW(B2:B10)-ROW(INDEX(B2:B10,1,1))+1),2)

get nth match with one criteria1

The detailed description for this formula, please continue reading: get the position of nth occurrence of a value in column

The SMALL function returns the position of the second occurrence of the string text “jenny” in the range B2:B10. If you want to get the third or nth match value in a range, just need to modify the nth in the formula as your need.

=INDEX(D2:D10, SMALL(IF(B2:B10=”jenny”, ROW(B2:B10)-ROW(INDEX(B2:B10,1,1))+1),2))

get nth match with one criteria1

The First INDEX function returns bonus value of the second match based on the position number returned by the SMALL function. So it returns $165.

2. Related Formulas

  • Find nth Occurrence with Multiple Criteria Using INDEX/MATCH
    If you want to find the nth occurrence with multiple criteria, you can use a combination with the INDEX function, SMALL function, nested IF function and ROW function to create a complex excel formula like this:=INDEX(Array,SMALL(IF(Range1…
  • 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.…
  • Lookup the Value with Multiple Criteria
    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.…
  • Lookup the Next Largest Value
    If you want to get the next largest value in another column, you can use a combination of the INDEX function and the MATCH function to create an excel formula..

3. 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 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])….

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.

find last value greater thatn x1.png

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
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.

find last value greater thatn x2.png

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])….

How to Extract the Column Header of the Largest Value in a Row in Excel

This post will guide you how to find the largest value in a row and return column header using VBA code or a formula in Excel. How to retrieve the column header of the largest value in a row with a formula in Excel. How to return the column title of the max value in the row in Excel.

The first method involves using a VBA user-defined function to create a custom formula that can be used to find the largest value in a given row and return the header of the column where that value is located.

The second method involves using a combination of built-in Excel functions to create a formula that can achieve the same result.

In this post, we will explore both methods and provide step-by-step instructions on how to implement them.

1. Video: Return the Column Header of the Largest Value in a Row

This video tutorial provides step-by-step instructions on how to return the column header of the largest value in a row in Excel using VBA code, a user-defined function, and a formula.

2. Return the Column Header of the Largest Value in a Row using Formula

Assuming that you have a list of data in range A1:D4, in which contain sales data for each month. And you want to find the highest value for each product and return the Month column header in your worksheet. How to do it. You can use an Excel formula based on the INDEX function, the MATCH function and the MAX function. Like this:

=INDEX($B$1:$D$1,MATCH(MAX(B2:D2),B2:D2,0))

You need to type this formula into cell E2 and press Enter key on your keyboard, and then copying this formula from cell E2 to range E3:E4, you would notice that you will get the column header for in every row.

extract column header of the largest value in row1

Let’s see how this formula works:

The INDEX function will return a reference of the cell at the intersection of a specific row and column in range B1:D1.

The MAX function will return the maximum number from a given range B2:D2. And pass the returned result into the MATCH function as its argument.

The MATCH function will return the relative position of the maximum value in the given range.

3. Return the Column Header of the Largest Value in a Row with User-defined Function

You can also create a user-defined function in VBA to extract the column header of the latest value in a row in Microsoft Excel. just do the following steps:

Step1: Open your Excel workbook and press Alt + F11 to open the VBA editor.

Step2: Insert a new module by right-clicking on the project and selecting “Insert > Module“.

Step3: Copy and paste the code above into the module. Save the module and return to your Excel sheet.

How to Extract the Column Header of the Largest Value in a Row in Excel vba2.png
Function GetLargestValueColumnHeaderbyExcelHow(rowRange As Range) As String
    Dim maxVal As Double
    Dim maxCol As Integer
    Dim headerRow As Integer
    Dim currentCell As Range
    
    headerRow = 1 'assuming headers are in the first row
    maxVal = -1E+20 'initialize max value as a very small value
    
    For Each currentCell In rowRange 'loop through each cell in the row range
        If currentCell.Value > maxVal Then 'check if current cell value is greater than max value
            maxVal = currentCell.Value 'update max value if necessary
            maxCol = currentCell.Column 'update max column if necessary
        End If
    Next currentCell
    
    GetLargestValueColumnHeaderbyExcelHow = Cells(headerRow, maxCol).Value 'return the column header of the max value
End Function

Step4: In a cell where you want to display the column header of the largest value in a row, enter the following formula, then press Enter to display the column header of the largest value in the selected row.

 =GetLargestValueColumnHeader(B2:D2)

Where B2:D2 is the range of cells representing the row you want to check.

How to Extract the Column Header of the Largest Value in a Row in Excel vba3.png

Step5: click on the AutoFill Handler down to cell E2,E3 to apply this formula.

How to Extract the Column Header of the Largest Value in a Row in Excel vba4.png

4. Conclusion

whether you prefer using VBA code or a formula, there are multiple ways to return the column header of the largest value in a row in Excel. With the help of a user-defined function, built-in functions such as VLOOKUP or INDEX/MATCH, or a combination of both, you can quickly and easily extract the information you need from your worksheet.

5. 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 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])…

How To Transpose Every N Rows of Data into Muliptle Columns in Excel

This post will guide you how to transpose data from rows to column with a formula in excel using both a VBA code and a formula. How do I transpose every N rows from one column to multiple columns in Excel.

In Excel, Transposing data is a common task, but when you want to transpose every N rows of data into multiple columns, it can be a bit trickier. In this post, we’ll show you how to use a VBA code and a formula to accomplish this task easily.

Assuming that you have a list of data in range A1:A10 in column A, and you want to transpose every 2 rows from column A to Mulitple columns. for example, you want to transpose range A1:A2 to C1:D1, A3:A4 to C2:D2. How to do it.

1. Video: transpose Every N Rows of Data into Multiple Columns

Here’s a video tutorial on how to transpose every N rows of data into multiple columns in Excel using a formula and VBA code.

2. Transpose Every N Rows of Data into Multiple Columns Using Excel Formula

You can also transpose every N rows of data into multiple columns in Excel using a formula based on the INDEX function, the Row function and the Column Function. Like as below:

=INDEX($A:$A,ROW(A1)*2-2+COLUMN(A1))

Type this formula into cell C1, and press Enter key on your keyboard, and then drag the AutoFill Handle to CEll D1.

How to Transpose every N rows from one column to multiple1

Then you need to drag the AutoFill Handle in cell D1 down to other cells until value 0 is displayed in cells.

How to Transpose every N rows from one column to multiple2

3. Transpose Every N Rows of Data into Multiple Columns with VBA Code

You can also use VBA code to transpose every N rows of data into multiple columns in Excel. Here’s a step-by-step guide:

Step1: Open the Excel workbook you want to work with and press “Alt + F11” to open the Visual Basic Editor.

How To Transpose Every N Rows of Data into Muliptle Columns vba 1.png

Step2: In the Visual Basic Editor, click on “Insert” from the menu bar and select “Module” to create a new module.

How To Transpose Every N Rows of Data into Muliptle Columns vba2.png

Step3: Copy and paste the following code into the module window.

How To Transpose Every N Rows of Data into Muliptle Columns vba3.png
Sub TransposeRows_excelhow()
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim LastRow As Long
    Dim NumCols As Long
    Dim DataRange As Range
    Dim DestRange As Range
    
    ' Set the number of rows to transpose into each column
    NumCols = Application.InputBox("Enter the number of rows to transpose into each column:")
    
    ' Select the range of cells to transpose
    On Error Resume Next
    Set DataRange = Application.InputBox("Select the range of cells to transpose:", Type:=8)
    On Error GoTo 0
    If DataRange Is Nothing Then Exit Sub
    
    ' Select the destination range for the transposed data
    On Error Resume Next
    Set DestRange = Application.InputBox("Select the destination range for the transposed data:", Type:=8)
    On Error GoTo 0
    If DestRange Is Nothing Then Exit Sub
    
    LastRow = DataRange.Rows.Count
    k = 0
    
    For i = 1 To LastRow Step NumCols
        k = k + 1
        For j = 0 To NumCols - 1
            DestRange.Offset(j, k - 1).Value = DataRange.Offset(i + j - 1, 0).Value
        Next j
    Next i
End Sub

Step4: Press “F5” or click on the “Run” button in the toolbar to run the code.

Step5: Enter the number of rows to transpose into each column, such as: 2,Click OK button.

Step6: Select the range of cells to transpose, such as: selecting range A1:A10. Click OK button.

How To Transpose Every N Rows of Data into Muliptle Columns vba5.png

Step7: Select the destination range for the transposed data, such as: Cell C1. Click on Ok button.

How To Transpose Every N Rows of Data into Muliptle Columns vba6.png

Step8: Once you have entered the required information, the selected range of cells will be transposed into multiple columns according to the number of rows you specified.

How To Transpose Every N Rows of Data into Muliptle Columns vba7.png

Step9: select the transposed data and press Ctrl +C to copy it. Then right click on a black cell and select “Transpose” from the Paste option menu list.

How To Transpose Every N Rows of Data into Muliptle Columns vba8.png

Step10: You can quickly and easily transpose every N rows of data into multiple columns using the TRANSPOSE function in Excel.

4. Conclusion

Transposing every N rows of data into multiple columns in Excel can be done using either a VBA code or a formula. Both the VBA code and the formula are effective methods for transposing every N rows of data into multiple columns in Excel. The best method to use will depend on the specific needs of your project and your level of comfort with coding in VBA.

5. 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 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 COLUMN function
    The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….

Excel Array Operation

We have all heard of arrays. Depending on the dimensionality, they are divided into one-dimensional arrays and two-dimensional arrays. Depending on the data type, they can also be divided into numeric arrays, logical arrays, and so on. The concatenation and operation of different arrays is an important part of excel formulas. Many people are still confused about the arithmetic operations of arrays. This article will introduce the basic operations between arrays, such as addition and multiplication.

Direct operations are performed on arrays by using operators instead of using functions. Since the components of arrays include numeric, textual, logical, and error values, arrays inherit the arithmetic properties of all types of data (except error values). Numeric and logical arrays can perform regular arithmetic operations such as addition, subtraction, multiplication, and division, while text arrays can perform concatenation operations.

Direct Operation Between Arrays and Values

Users can perform an arithmetic operation between an array and a number. The operation returns an array in the same dimension. This array contains the same number of elements. For example, if an M*1 array and a number are added, the new array is also an M*1 array.

See the example below. For vertical array, elements are separated by semicolon, and for horizontal array, elements are separated by comma.

Array: {1;2;3}

Value: 4

Operation: {1;2;3}+4

Result: {5;6;7}

Excel Array Operation1

The gif shows the operation between a vertical array {1;2;3} and a number {4}. The new array is also a vertical array contains three numbers {5;6;7}.

In some versions of excel, the area where the new array is to be saved should be selected in advance, then enter the operation in the first cell, and after entering the operation, press Shift+Control+Enter to return to the new array. However, in some versions, such as office 365, the user can enter the operation directly in E2 and then press Enter and the new array will be saved correctly in E2: E4.

The formula operation process is shown in the figure:

Excel Array Operation1

Direct Operations Between Two One-dimensional Arrays with Same Direction

For one-dimensional array, based on the directions we can divide array into one-dimensional vertical array and one-dimensional horizontal array.

When two one-dimensional arrays are operated directly, the elements in the same position will be operated correspondingly, and a new array is generated after the operation, which has the same direction and the same number of elements as the original array.

See the example below.

Array1: {1;2;3}

Array2: {4;5;6}

Operation: {1;2;3}+{4;5;6}

Result: {5;6;9}

Excel Array Operation1

The formula operation process is shown in the figure:

Excel Array Operation1

Here we should note that two one-dimensional arrays should have the same number of elements, otherwise some of the returned values will have an error #N/A.

See the example below.

Array1: {1;2;3}

Array2: {4;5;6;7}

Operation: {1;2;3}+{4;5;6;7}

Result: {5;6;9;#N/A}

Exceeded returns error #N/A. In this example, there is no value in array 1 corresponding to the fourth number {7} in array 2.

Excel Array Operation1

In addition to basic addition, multiplication, etc., arrays can be combined into a new array, an application that is widely used in everyday work when searching for something based on multiple conditions. The following example will show you the application in working life.

Example: Query the effort based on two conditions milestone and project.

Excel Array Operation1

In F3 enter the formula :

=INDEX(C2:C13,MATCH(F1&F2,A2:A13&B2:B13,0)).

In this example, the two conditions are “Milestone: 3” and “Project: Project B”, these two conditions are saved in columns A2:A13 and B2:B13. We use “&” to concentrate the two texts into one text and the two arrays into one new array. Then the formula uses the MATCH function to search for the location of the set “F1&F2” in the new array “A2:A13&B2:B13”, and the INDEX function returns the effort corresponding to “F1&F2” in C2:C13.

The arrays are as follows:

=INDEX({1200;1000;1100;1500;1200;1300;1400;1300;1000;1100;1500;1400},MATCH("3Project B",{"1Project A";"1Project B";"1Project C";"2Project A";"2Project B";"2Project C";"3Project A";"3Project B";"3Project C";"4Project A";"4Project B";"4Project C"},0))
Excel Array Operation1

Operation Between Two One-dimensional Arrays with Different Directions

Array1 is a vertically oriented array, it has one column but M rows, so it is an M*1 array with elements listed in each of the M rows; Array2 is a horizontally oriented array, it has one row but N columns, so it is a 1*N array with elements listed in each of the N columns.

The vertical array M*1 and the horizontal array 1*N are operated directly as follows: each element of array 1 is operated separately from each element of array 2, returning a two-dimensional array of M*N.

See the example below.

Array1: {1,2,3}

Array2: {4;5;6}

Operation: {1,2,3}+{4;5;6}

Result: {5,6,7;6,7,8;7,8,9}

Excel Array Operation1

The formula operation process is shown in the figure:

Excel Array Operation1

Operation Between One-dimensional Array and Two-dimensional Array

If a one-dimensional array and a two-dimensional array have the same dimensions in the same direction, for example the two-dimensional array is M*N, the one dimensional array is M*1 or 1*N, then they can be operated directly as follows: elements inside each array are operated correspondingly to form a new two-dimensional array of M*N.

See the example below.

Array1: {1;2;3}

Array2: {1,4;2,5;3,6}

Operation: {1;2;3}*{1,4;2,5;3,6}

Result: {1,4;4,10;9,18}

Excel Array Operation1

The formula operation process is shown in the figure:

Excel Array Operation1

If the dimensions of the one-dimensional array and the two-dimensional array are different, the result will contain the error value #N/A.

Array1: {1;2;3}

Array2: {1,4;2,5;3,6;1,1}

Operation: {1;2;3}*{1,4;2,5;3,6;1,1}

Result: {1,4;4,10;9,18;#N/A,#N/A}

Operation Between Two-dimensional Arrays

Two two-dimensional arrays with the same dimension can be operated directly on two elements at the same position and return a new two-dimensional array with the same dimension. For example, operating two arrays M*N will form a new array M*N.

See the example below.

Array1: {1,4;2,5;3,6}

Array2: {1,4;2,5;3,6}

Operation: {1,4;2,5;3,6}*{1,4;2,5;3,6}

Result: {1,16;4,25;9,36}

The formula operation process is shown in the figure:

If the sizes of the two two-dimensional arrays involved in the operation do not match, the resulting array takes the largest column and row of the two arrays as the new array dimensions, but the part of the array that exceeds the smaller dimension generates the error value #N/A.

Break ties with helper COUNTIF and column

Suppose you got a task to adjust the values that contain the ties; what would be your first attempt to break the ties of the given value? If you are wondering about doing this task manually, let me add that you will end up exhausted, and there is a probability that you will not complete this task on time. Moreover, besides consuming a lot of time and energy, it also seems to be a cumbersome task to break the ties in the values manually.

But don’t worry about it because you have just landed on the right article, where you will get concise information to break the ties in just a matter of seconds.

3 Break ties with helper column and COUNTIF1

So without any further ado, let’s get started.

General formulation

The formula for breaking ties in values using column and COUNTIF is as follows.

= (COUNTIF(range,A1)-1)*adjustment + A1

 Explanation of Syntax

You must be aware of the syntax used in the above formula to use it to complete your task.

CountIF: In Excel, the COUNTIF function counts the number of cells that fulfill a specific condition or criteria. Learning how to utilize the COUNTIF Excel function is helpful because we frequently want to know how many objects in a huge collection are alike in a specific manner. The COUNTIF function is commonly used as a fast fix.

  • The plus operator (+): This operator is used to add the values.
  • Minus Operator (-): Use this symbol to subtract any two numbers.
  • Multiplication (*): In this symbol, any two values or numbers will be multiplied.
  • The comma symbol (,): This symbol is a separator that aids in the separation of a list of values.
  • Parenthesis (): This symbol’s primary function is to group the elements.

Summary

To break ties, use a helper column and the COUNTIF function to alter values so that they don’t have duplicates and don’t result in ties. The formula in D5 in the example is:

= (COUNTIF($B$3: B3, B3)-1)*0.01 +B3
3 Break ties with helper column and COUNTIF1

 When you employ SMALL, LARGE, or RANK functions to rank highest and lowest values, you may encounter ties because the data contains duplicates. In this situation, one method for breaking ties is to create a helper column with altered values, then rank those values instead of the originals.

The reasoning used to alter values in this example is random – the first duplicate value will “win,” but you may modify the formula to employ appropriate logic for your specific scenario and use case.

Explanation

This formula relies on the COUNTIF function and an increasing range to count occurrences of values. COUNTIFS uses the expanding reference to deliver a running count of occurrences rather than a total count for each value:

=COUNTIF($B$3: B3, B3)
3 Break ties with helper column and COUNTIF1
= (COUNTIF($B$3: B3, B3)-1)*0.01
3 Break ties with helper column and COUNTIF1

The result is then multiplied by 0.01 after being removed by 1 (which makes the count of all non-duplicate values zero). This is the “adjustment,” It is purposefully minimal to not significantly influence the original value.

“excel” and “ppt” both have the same sales of 500 in the scenario. Because excel is first in the list, the running count of 500 equals 1 and is canceled out by removing 1; thus, the estimate in the helper column remains unchanged:

However, the running count of 500 for ppt product is 2, therefore the estimate is adjusted:

=B7+0.01

Finally, instead of the original values in columns G and H, the corrected values are utilized for ranking. The formula in H3.

=LARGE($C$3:$C$8,F3)
3 Break ties with helper column and COUNTIF1

G3’s formula is as follows:

=INDEX($A$3:$A$8,MATCH(H3,$C$3:$C$8,0))
3 Break ties with helper column and COUNTIF1

Column of temporary assistance

Suppose you don’t want to use a helper column in the final solution. In that case, you can use it temporarily to get calculated values, then use Paste Special to convert values into the original cell range.

3 Break ties with helper column and COUNTIF1

Then you can delete the helper column.

3 Break ties with helper column and COUNTIF1

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 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)…

Find the Closest Data to the Data Provided in Excel

In our daily work, we may encounter such an issue that to find the closest value to a certain value. In fact, Excel internal functions can help us solve this problem. In today’s article, we will show you how to find the student whose score value is closet to the score provided with help of Excel INDEX/MATCH/MIN/ABS functions.

Look at the following example, we want to know who has a score closest to score “150” or “100”.

Find the Closest Data to the Data Provided1

Expect Result:

Find the Closest Data to the Data Provided1

GENERAL FORMULA

The general formula for this case is

=INDEX(Data,MATCH(MIN(ABS(Data-ProvideValue)),ABS(Data-ProvideValue),0))

In the general formula, you can replace Data and ProvideData with your own range or data reference. This is an array formula and we need to press “control + shift + enter” after entering the formula.

In the above example, the formula is

=INDEX(Student,MATCH(MIN(ABS(Score-D2)),ABS(Score-D2),0)).

In the formula, Data is “Student” list (A2:A9, named “Student”); there are two provided values, 150 in D2 and 100 in D3; In this example, we want to find the closest score to the provided score (in D2 and D3) from the “Scores” list and retrieve the matching student name from the “Students” list that will be returned by the formula and filled in correctly in E2 and E3. Enter above formula in C8, then copy down the formula, the matching students are filled in properly.

Notes:

If we want to only obtain the score value instead of the student, we can just change the lookup array from Student to Score in this formula.

=INDEX(Score,MATCH(MIN(ABS(Score-D2)),ABS(Score-D2),0))

 EXPLANATION

For the formula, the core is the usage of Excel INDEX and MATCH functions combination. Before we can explain this formula, we need to know these two functions.

MATCH is an Excel function for locating the position of a query value in a row, column, or table. INDEX is used to return the value at a certain position. As you can see, the MATCH function can provide a relative position of a value within a range, and INDEX can provide a suitable value based on the position provided, so typically, the MATCH and INDEX functions are used together to retrieve a value at a matching position.

Syntax:

=MATCH(lookup_value, lookup_array, [match_type]) (match type 0=exact match)

=INDEX(array, row_num, [column_num])

In this example, using E2 as an example, we want to return the name of the student whose score retrieved from the student list is closest to the score 150 provided in D2, so for INDEX function in this formula, the array is the named range “Student” (A2:A9). We expand this array in the formula bar, the array is generated:

{"Danni";"Michelle";"Riya";"Emilia";"Nova";"Ari";"Leo";"Maren"}

Find the Closest Data to the Data Provided3

In this INDEX function, the parameter “row_number” is obtained by executing another Excel MATCH function. MATCH function will delivery its result to INDEX as row number. The hard work in this case is to find the closest score in Score list to the data provided in D column and obtain the relative position of this score in Score list. To resolve this issue, we use Excel ABS and MIN functions in MATCH expression to obtain the minimum difference between the data and the data provided, and with their help, MATCH function can return the position of the closest score. To let you know how it works step by step, we will explain the expression from inside to outside as the result of the internal function will be delivered to the external one.

First, let’s get to know what the role ABS and MIN functions play in MATCH function.

 ABS Function is used to return the absolute value of an integer.

Syntax:

=ABS(value)

 MIN Function is used to return the smallest value in supplied data

Syntax:

=MIN(number1, [number2], ...)

 In this example, MIN(ABS(Score-D2)) is the lookup value of the MATCH function. ABS(Score-D2) is the lookup array. Match type is 0, so the MATCH function returns an exact match.

Notes:

We all know that the smaller the difference between two values, the closer the two numbers are to each other. ABS(Score-D2) provides an array that save the differences between each value in “Score” and the value provided in D2, the differences may be negative or positive, but ABS function will convert the negative ones to positive numbers, so this function returns an array only contains positive numbers; And MIN(ABS(Score-D2)) provides the smallest difference among all differences.

 For ABS(Score-D2), since the values saved in “Score” are vertically aligned, “Score” is a vertical array.; so (Score-D2) is also a vertical array. This step is done to get the difference between the two values.

Expand Score and D2 in the formula bar:

Score: {134;142;110;96;120;98;144;108}

D2:150

Find the Closest Data to the Data Provided3

Calculate (Score-D2) in Excel formula bar and get below array:

{-16;-8;-40;-54;-30;-52;-6;-42}

Find the Closest Data to the Data Provided3

Use Excel ABS function to convert all negative numbers to positive numbers.

{16;8;40;54;30;52;6;42} – This array is the lookup array for MATCH function

Find the Closest Data to the Data Provided3

For MIN(ABS(Score-D2)), the result of ABS function (numbers in the array above) is also delivered to MIN function:

=MIN({16;8;40;54;30;52;6;42})

Find the Closest Data to the Data Provided3

The Excel MIN function will extract the smallest value in the array.

Find the Closest Data to the Data Provided3

Now, for MATCH function, the lookup value and lookup array are obtained after calculating ABS and MIN expressions.

=MATCH(6,{16;8;40;54;30;52;6;42},0)

As mentioned above, Excel MATCH function returns the position of a certain value, so in this case, MATCH function returns the row number of value “6” in array {16;8;40;54;30;52;6;42}. Obviously, relative to this array, 6 is in row 7.

Find the Closest Data to the Data Provided3

Now we come to the outermost INDEX function:=INDEX({“Danni”;”Michelle”;”Riya”;”Emilia”;”Nova”;”Ari”;”Leo”;”Maren”},7)

 

In this formula, MATCH function delivered row number 7 to INDEX function, INDEX function returns a value in an array based on a provided row number. In this array which consists of student names, the seventh name is “Leo”, so this is the final result for this formula. After entering “Ctrl+Shift+Enter”, “Leo” is displayed in cell E2.

Find the Closest Data to the Data Provided3

Copied down the formula to E2, we can obtain “Ari” in the same way.

Find the Closest Data to the Data Provided3

This article not only introduces the joint application of INDEX and MATCH, but also the use of MIN and ABS functions, including how to apply the combination of MIN and ABS functions to find the minimum difference between values. Readers can design their own formulas according to the actual situation.

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 MIN function
    The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….
  • Excel ABS Function
    The Excel ABS function returns the absolute value of a number.The ABS function is a build-in function in Microsoft Excel and it is categorized as a Math and Trigonometry Function.The syntax of the ABS function is as below:=ABS (number)…

 

 

 

Creating a Table with Automatic Row Numbering

create table with automatic row number1

When creating an Excel Table, you may use a calculation relying on the ROW function to insert row numbers automatically. The formula in A2, which has been copied down, is as follows:

=ROW()-ROW(Table1[#Headers])

Note: the default table name is Table1. And #Headers is the header in the Table1.

Let’s See How This Formula Works:

When there is no parameter, the ROW function will return the row number for the current row. If A cell reference is supplied, and ROW function will retrieve the number of rows of the selected cell in question. When a range is specified, ROW returns the row number corresponding to the first row in the range.

For the above formula, there is no argument supplied to ROW, the first ROW function returns 2, located in cell A2. The second ROW function makes use of a structured reference, as follows:

=ROW(Table1[#Headers])

create table with automatic row number1

There is no header row

The formula above works well as long as there is a header row in the table; however, the formula will fail if the heading row is disabled. If you are dealing with a table that does not have a header row, you may make use of the following method:

=ROW()-INDEX(ROW(Table2),1,1)+1

As in the previous formula, the initial ROW function will return the row that is currently being shown. Using the INDEX function, the first cell in the range Table2 (cell B2) is passed on to the second-ROW feature, which always returns the number 5. The following is how the formula works for the first three rows of the table:

create table with automatic row number1

Regardless of whether or not the header row is enabled, this equation will continue to operate correctly.

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 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])….

Get Address of First Cell in Range

We will learn how to get address of first cell in range in this post. At Instance, we must determine the address of first cell in range to use as the data table’s value. For the DATA table, we have a range of value values in the form of an array that is sent to the formula as input, and therefore the formula returns the first cell value.

We will be using the following functions in this article:

The next sections include information on the functions mentioned before that we will use.

ADDRESS retrieves the address of a cell using the row and column numbers.

Syntax:

=ADDRESS(row_num, col_num, [abs_num] )
  • Row_num: The Row number
  • Col_num: The Column number
  • [abs num]: [Optional] integer, one of the following values: 1, 2, 3, or 4. If this parameter is ignored or set to 1, the returned address will be absolute, e.g. $A$1.

Now we’ll combine these functions into a formula. We’ll begin with a data table. We need to determine the data table’s initial cell address.

Utilize the following formula:

=ADDRESS(ROW(table_array), COLUMN(table_array))

Variables:

  • table array: array representation of a data table

Examples:

Let us validate this formula by applying it to an example.

Here we have a data table and we need to use the formula to get the first cell address in the same data table.

Address of first cell in range1

Use the following Formula:

= ADDRESS(ROW(A1:E9), COLUMN(A1:E9))

Address of first cell in range1

Note: A1:E9 is array of tables

Let’s See How This Formula Works:

= COLUMN(A1:E9)

Address of first cell in range1

The COLUMN function returns the value of the first column in the table array.

= ROW(A1:E9)

Address of first cell in range1

The ROW function returns the index of the first row in the table array.

The ADDRESS function accepts the row number and column value parameters and returns the row’s and column’s absolute references.

The array sent to the function is denoted by cell reference. To get the result, press Enter.

As you can see, the formula works correctly since it delivers the value for the first cell in the data table.

Alternatively, you may use the CELL function:

CELL is an Excel built-in information function. Excel’s Cell function saves all the data in a specified cell and returns the cell’s info type. Using the follow formula:

=CELL("address", [array_reference])

or

=CELL("address",INDEX(A1:E9,1,1))

Address of first cell in range1

Note:

  • [array_reference] : table array given as reference.

As seen by the above calculation, you may use the first cell value as a reference.

The INDEX function can be used to get cell reference to the first cell in the given range by giving INDEX 1 for row number and 1 for column number.

Address of first cell in range1

The ADDRESS function may be used in conjunction with the ROW, COLUMN, and MIN functions to get the address of the first cell in a range. I hope this post on finding the get address of first cell in range in Excel was informative. It’s extremely easy and very helpful for people who use excel regularly.

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 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 ADDRESS function
    The Excel ADDRESS function returns a reference as a text string to a single cell.The syntax of the ADDRESS function is as below:=ADDRESS (row_num, column_num, [abs_num], [a1], [sheet_text])….
  • Excel COLUMN function
    The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….
  • Excel CELL function
    The Excel CELL function returns information about the formatting, location, size, or contents of a cell.The syntax of the CELL function is as below:= CELL (info_type,[reference])…
  • Excel MIN function
    The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….

Filter or Extract with a Partial Match

Assume you have a table consisting of a few cells having few values, and you want to filter out the set of records with the partial match. You might take it easy and would prefer to manually filter out the desired partial matching values into another table without any need for the formula; then congratulations because you are thinking right.

But let me add that it would be a big deal while dealing with a bulk of data in the table, and then doing this bulky task manually would be a foolish decision.

But there isn’t any need to worry about it because after carefully reading this article filtering out the set of records with the aid of partial matches will be a piece of cake for you.

filter with partial match1

So let’s get straight into it!

General Formula:

You can use the FILTER function in combination with the SEARCH function to choose data records based on a partial match. The formula in E4 is written as follows:

=FILTER(Data_range,ISNUMBER(SEARCH(B2,Filter_range))*(B2<>""),"Not Found")

Note: Data_range is name range for A2:C9, and Filter_range is antoehr name range for B2:B9.

filter with partial match1

Let’s See How This Formula Works

The motive is to extract a collection of records that match a partial text string in this example. We match one column in the data range A2:C9 or the “Region” column. The FILTER function (new in Excel 365) retrieves matched data from a range based on a logical filter, which is at the heart of this formula:

=FILTER(filter_data,filter_logic)

The task in this example is to build the logic required to match records based on a partial match. Because the FILTER function does not handle wildcards, we must use an alternative technique. In this situation, we use the SEARCH function in conjunction with the ISNUMBER function as follows:

=ISNUMBER(SEARCH(B2,Filter_range))

filter with partial match1

The SEARCH function seeks text input in cell E2 within the Filter_range name range. SEARCH returns the position of a result in the text if it finds one.

If SEARCH formula does not yield any results, it returns the #VALUE! error:

We have a match if SEARCH returns a number. Otherwise, we don’t have a match. We wrap the SEARCH function within the ISNUMBER function to transform this result into a simple TRUE/FALSE value. Only when SEARCH returns a number will ISNUMBER return TRUE.

We aren’t utilizing a wildcard like (“*”) to achieve a partial match, but the SEARCH + ISNUMBER combination acts similarly. SEARCH function will return a number if the search string is found anywhere in the text, and ISNUMBER will return TRUE if the search string is found anywhere in the text.

=FILTER(Data_range,ISNUMBER(SEARCH(B2,Filter_range)))

filter with partial match1

We now have a workable formula, but we still need to clean up a few things. First, if the FILTER function returns no results, it will produce a #CALC! error. We would add a text message for the “if_empty” argument to deliver a friendlier message:

=FILTER(Data_range,ISNUMBER(SEARCH(B2,Filter_range))*(B2<>""),"Not Found")

FILTER will now return “Not Found ” if the search text is not found.

Finally, we must deal with the circumstance where the search string in E2 is blank. Surprisingly, if the search text is an empty string, the SEARCH function will return the value 1.

If field B2 is empty, FILTER will return all results since ISNUMBER will joyfully return TRUE for number 1. To avoid this behavior, we add the following logic to the original logical expression:

=ISNUMBER(SEARCH(B2,Filter_range))*(B2<>"")

filter with partial match1

When B2 is not empty, the expression B2<>”” yields TRUE; otherwise, it returns FALSE. By the original SEARCH + ISNUMBER expression, when we would multiply the results of this expression, all TRUE results are “canceled out” when B2 is empty. This is a variation on Boolean logic.

 

Extract All Partial Match Using Index and Match function

Only Excel 365 supports the FILTER feature. It is feasible to put up a partial match formula in previous versions of Excel to produce more than one match, but it is more complicated. This following formula demonstrates one method based on INDEX and MATCH.

=INDEX($B$1:$B$5,AGGREGATE(15,6,(ROW($B$1:$B$5)-ROW($B$1)+1)/ISNUMBER(SEARCH($D$1,$B$1:$B$5)),E2))

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 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 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 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 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 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])…
  • Excel AGGREGATE function
    The Excel AGGREGATE function returns an aggregate in a list or database and ignore errors or hidden rows. The syntax of the AGGREGATE function is as below:= AGGREGATE(function_num, options, ref1,[ref2])…
  • Excel Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…

Find and Replace Multiple Values

This post will guide you how to find and replace multiple values at once with VBA macro or using formula  in Excel. How do I make multiple find and replace in Excel.

Suppose that you have a few cells containing few values and you want to find and replace two or three values; then you might think that it’s not a big deal; because you would prefer to do it with the help of the Find and Replace built-in feature of excel, then congratulation because you have supposed to choose the right way for this task.

There isn’t any doubt that you can find and replace values in excel by its Find and Replace built-in feature, which works entirely perfect for two or three values, but when you would have the bulk of values, and you want to find and replace them, then doing this task manually by the aid of this Find and Replace built-in feature would be a stupid decision because you would get tired of it and would never complete your work on time.

But there isn’t any need to worry about it because, luckily, there are a few effective methods for finding and replacing multiple values in a few seconds.

After reading this article carefully, you would get to know the several ways to find and replace multiple words, individual characters, or strings so that you can select the best one according to your needs and ease.

find and replace multiple values1

So let’s get straight into it!

Find and Replay Multiple Values using Formula

 General formula:

The formula given below would help you out for finding and replacing multiple values within few seconds:

=SUBSTITUTE(SUBSTITUTE(B2,INDEX(finding_values,1),INDEX(replacing_values,1)),INDEX(finding_values,2),INDEX(replacing_values,2))

or

=SUBSTITUTE(SUBSTITUTE(B2,finding_values,replacing_values), finding_values, replacing_values))

find and replace multiple values1

Syntax Explanation:

Before we dive into the formula for getting the job done effectively, we need to understand each syntax so that we can know how each syntax helps to find and replace the multiple values:

  • SUBSTITUTE: This function lets users replace current text in a text string with new text when they desire to replace text based on its content rather than its position.
  • INDEX: The INDEX function in Excel is used to repeat characters a specified number of times.
  • B2: This is the input value.
  • Find: This command specifies the text to be found in the input range.
  • Replace: It represents the text that is being replaced.
  • The comma sign (,): is a separator that may separate a list of values.
  • Parenthesis (): The primary purpose of the parenthesis () symbol is to organize the components.

Let’s See How This Formula Works:

As I said above, there are a few ways to find and replace multiple values in Excel in a matter of seconds, and one of the most popular and simple methods is to utilize the nested SUBSTITUTE function.

The logic of this formula is very simple: you write a few individual functions to replace an old value with a new one. Then you nest those functions one inside the other so that each subsequent SUBSTITUTE uses the output of the previous SUBSTITUTE to find the next value.

=SUBSTITUTE(SUBSTITUTE(Text_values,finding_values,replacing_values), finding_values, replacing_values))

Assume you wish to replace the region names in the B2:B9 list of places with the replacing names.

For getting the desired output, input the old values in E2:E3 and the new values in F2:F3, as seen in the image and formula below. Then, in E5, enter the following formula:

= SUBSTITUTE(SUBSTITUTE(B2, E2, F2), E3,F3)

You’ll have completed all of the replacements at once:

Please remember that the above method only works in Excel 365, which supports dynamic arrays.

In pre-dynamic versions of Excel 2016, Excel 2019, and prior,type the following formula:

= SUBSTITUTE(SUBSTITUTE(B2, $E$2, $F$2), $E$3,$F$3)

Also, note that in this case, we lock the replacement values with absolute cell references to ensure that they do not shift when copying the formula down.

find and replace multiple values1

The SUBSTITUTE function is case-sensitive, so you must type the old values (old text) in the same letter case as the original data.

This method has a significant drawback: It should only be used for a limited number of find/replace values. This nested function becomes extremely difficult to manage when you have dozens of values to replace.

Benefits: Simple to deploy; compatible with all Excel versions

Find and replace multiple values using XLOOKUP Function

The XLOOKUP function is very efficient and helpful while replacing the entire cell content rather than just a portion of it.

Assume you have a list of region in column B and want to replace all the “East” or “West” regions as “northeast” or “northwest” values. Enter the following formula in B2:

=XLOOKUP(B2,$E$2:$E$3,$F$2:$F$3,B2)

The formula does the following when translated from Excel to human language:

It searches the B2 value (lookup value) in E2:E3 (lookup array) and returns a match from F2:F3 (return array). If the original value cannot be retrieved, take it from B2.

find and replace multiple values1

Because the XLOOKUP function is only accessible in Excel 365. However, you can easily replicate this behavior using a combination of IFERROR or IFNA and VLOOKUP:

=IFNA(VLOOKUP(B2,$E$2:$F$3,2,FALSE),B2)

find and replace multiple values1

Unlike the SUBSTITUTE, the XLOOKUP and VLOOKUP functions are both not case-sensitive, which means they search for lookup values regardless of letter case.

 Find And Replace Multiple Values with VBA Macro

Assuming that you have a list of data in range B1:B6, and you want to find multiple values and replace those value with different values. For example, find “excel” string and replace its as excel2013, and find “word’’ string and replace its as word2013, and so on. How to achieve it. You should use an excel VBA macro to quickly find and replace multiple values. 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.

Get the position of the nth using excel vba1

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

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

convert column number to letter3

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

find replace multiple values1

Sub ReplaceMulValues()
    Dim myRange As Range, myList As Range
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one range to be searched", "Find And Replace Multiple Values", myRange.Address, Type:=8)
    Set myList = Application.InputBox("select two column range where find/replace pairs are:", "Find And Replace Multiple Values", Type:=8)
    For Each cel In myList.Columns(1).Cells
        myRange.Replace what:=cel.Value, replacement:=cel.Offset(0, 1).Value
    Next
End Sub

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

find replace multiple values2

#6 Select one range to be searched

find replace multiple values3

#7 select two column range where find/replace pairs are

find replace multiple values4

#8 let’s see the result.

find replace multiple values5

Related Functions

  • 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 IFERROR function
    The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)….
  • Excel VLOOKUP function
    The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….
  • 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 XLOOKUP function
    This tutorial will show you how to filter out or extract the top n values from the list having few values using Filter function or XLookup function in Excel The syntax of the xlookup function is as below:=XLOOKUP(lookup value, lookup array, return array, [if not found], [match mode], [search mode])…

Excel XLOOKUP Function

Excel XLOOKUP Function was added into Excel as a beta feature in August 2019 and is now accessible exclusively in Microsoft 365. (as of July 2021). However, if you fall into this category and often deal with big sets of data in Excel, understanding this method is worthwhile. In this lesson, we will demonstrate how to use the XLOOKUP function to significantly simplify your data search.

What exactly is Excel XLOOKUP formula?

The Excel XLOOKUP function is a member of the family of lookup and reference functions. It is one of the most helpful features of Microsoft’s famous spreadsheet program. XLOOKUP is the simplest method for finding particular data items inside a cell range. The items included inside a previously set cell range are formatted. Does this sound familiar? The VLOOKUP function likewise operates on this concept. However, the more versatile Excel-XLOOKUP function allows you to look up not just one, but several items. Additionally, you may search for values vertically and horizontally inside your sheet.

In practice, what does this mean? Consider the following scenario: you have a digital client database in the form of an Excel file and are seeking for the address and phone number of a certain individual. With XLOOKUP, you can now search for related items by name and instantly see the needed information. It makes no difference if the sought data are included in a column, row, or table on another page. This indicates that the XLOOKUP function supersedes not only the VLOOKUP but also the HLOOKUP functions.

XLOOKUP syntax Function

The syntax of the XLOOKUP function is identical to those of VLOOKUP and HLOOKUP. If you’ve ever used them, you’ll appreciate how much more convenient XLOOKUP is. The following is the Excel syntax for the XLOOKUP function:

=XLOOKUP(lookup value, lookup array, return array, [if not found], [match mode], [search mode])

The XLOOKUP function accepts up to six parameters, the values of which are shown below.

  • lookup value (required): the value you’re looking for.
  • lookup array (required): the array in which the lookup value should be located.
  • return array (required): the array from which you want to retrieve and return the values when the lookup value is discovered.
  • [if not found] (optional): If no match is discovered, this value is returned.
  • [match mode] (optional): This option specifies the sort of match that you are searching for. There are several ways to specify it:
  • 0 – It searches for an exact match, and the result must precisely match the lookup array value. When not specified, it is also set as the default.
  • -1 – It searches for an exact match and then returns to the next lower value.
  • 1 – It searches for an exact match and then advances to the next bigger number.
  • 2 – It performs partial matches using wildcards, where the characters *,?, and have particular significance.

[search mode] (optional): Used to specify the lookup array’s XLOOKUP search mode. There are several ways to specify the same thing:

  • 1– Begin the search with the first item. When nothing is supplied, it is defaulted to default.
  • -1 – Conducts a reverse search, beginning with the last item.
  • 2 – Conducts a binary search in the lookup array in which the data must be sorted ascending. If the data is not properly organized, it may result in mistakes or incorrect outcomes.
  • 2 – Conducts a binary search in the lookup array, sorting the data in ascending order. If the data is not properly organized, it may result in mistakes or incorrect outcomes.

XLOOKUP’s Advantages and Disadvantages In Excel, the XLOOKUP function retains some of its benefits over VLOOKUP and INDEX/MATCH. However, it does have certain drawbacks.

The XLOOKUP Function’s Advantages

  1. It operates in both vertical and horizontal directions.
  2. Three inputs are required, rather than the four required by the VLOOKUP and INDEX MATCH methods.
  3. By default, an exact match is used.
  4. Utilize wildcards to conduct partial match lookups.
  5. Can execute descending order lookups.

INDEX MATCH now uses a single function rather than two.

The XLOOKUP Function’s Drawbacks

  1. Optional arguments may seem complicated to novices.
  2. Can take longer when two ranges are selected and the spreadsheet has an excessive number of cells.
  3. When the lookup and array results are not the same length, an error is returned.
  4. Both lookup and return ranges must be remembered.

How to Use Excel’s XLOOKUP Function

The XLOOKUP function is comparable to Excel’s LOOKUP function. XLOOKUP may be used by simply specifying the cell references for the function to act on.

Alternatively, you may utilize the top-level “Formula bar” box to input the XLOOKUP function syntax.

What makes XLOOKUP superior?

XLOOKUP simplifies and reduces the likelihood of errors in Excel’s most frequently used formulas. Simply type =XLOOKUP(what you're looking for, the list, the result list) and you’ll receive the result (or a #N/A error if the value is not found).

By default, this function looks for an exact match: One of the drawbacks of VLOOKUP is that you must provide FALSE as the last argument in order to get the right result. XLOOKUP corrects this by defaulting to precise matches. If desired, you may alter the lookup behavior using the match mode option.

The fourth parameter is used to accommodate the value not found situation. To suppress errors in the majority of business cases, we are compelled to encapsulate our lookup formulae with IFERROR or IFNA formulas. XLOOKUP has a fourth argument (described in further detail below) that allows you to choose the default output to use if your value is not found.

XLOOKUP includes extra parameters for searching for unusual circumstances. You may search from the top or bottom of a list, use wildcards, or use quicker alternatives for searching sorted lists.

It outputs a reference, not the value. While this may seem insignificant to casual Excel users, professional Excel users’ eyes light up when they find a formula that returns references. That is, you may mix XLOOKUP results with other formulae in novel ways.

It’s so much cooler to type; all you have to do is type =XL. I’m not sure whether this is a lucky coincidence, but saying =XL generates this formula.

Related Functions

  • Excel IFERROR function
    The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)….
  • Excel VLOOKUP function
    The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….
  • 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])….

Extract or Filter Top n values

You might have been through this kind of situation where you need to filter out the top n values from the list having few values, and I am also pretty sure about it that you might have chosen to do it manually, which is also a great choice when you have only a few values in a list, and you want to filter out the top n values.

But if you are dealing with multiple values in the list and you want to filter out the top n values, then in such a situation doing these tasks manually would be a foolish act because by doing it manually, there are 90% chances that you would 100% get tired of it and can’t complete your task at the right time.

But don’t be worry about it because after carefully reading this article filtering out the top n values from the list containing multiple values would become a piece of cake for you.

So let’s dive into the article to take you out of this fix.

filter on top n values

General Formula


The Following formula would help you Filter on top values in MS Excel:

=FILTER(input_range,condition_range>=LARGE(condition_range,n))

filter on top n values

Syntax Explanations


Before going into the explanation of the formula for getting the work done efficiently, we must understand each syntax which would make it easy for us that how each syntax contributes to filtering out the top n values in MS Excel.

  • Filter: This function contributes to narrowing down or filtering out a range of data on the user-defined criteria.
  • input_range: In your worksheet, it represents the input ranges.
  • Comma symbol (,): In Excel, this comma symbol acts as a separator that helps to separate a list of values.
  • condition_range: In Excel, the range is nothing but the difference between highest and lowest values
  • Parenthesis (): The core purpose of this Parenthesis symbol is to group the elements and separate them from the rest of the elements.
  • Greater than Equal To Symbol (>=): Greater than Equal To Symbol (>=) is used to identify the values which are either greater than or equal to.
  • LARGE: Large function is used to get the higher arguments.

Let’s See How This Formula Works


For instance, you got a task in which there is a table where you have candidates of two groups (i.e., group A and group B ) and which are assigned to a particular sales value, now you want to filter out the top 5 candidates with the higher sales, now let’s analyze that how to to write the formula and how this formula would do it.

As to filter on the top n values, we would write the formula according to the given list like:

=FILTER(TotalData,Sales>=LARGE(Sales,5))

The FILTER function is used in this formula to obtain data based on a logical test built using the LARGE function.

The LARGE function is a straightforward method for obtaining the nth largest number in a range. Specify a range as the first parameter  and an n value as the second argument:

=LARGE(Sales,5)// Fifth largest

filter on top n values

This snippet is used to build the logic for FILTER in this example:

=Sales>=LARGE(Sales,5)

filter on top n values

Moreover, the above snippet would return TRUE if the sales is larger than or equal to the fifth-highest sales. Because there are 9 cells in the range A2:A10, this equation yields an array of 9 TRUE and FALSE values in the form of the following array:

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

This array is returned as the included parameter to the FILTER function, which filters the data. Only rows with the result TRUE are included in the final output.

The algorithm in E2 produces the highest five sales in the total data, which fall into the range E2:G6.

Sort results by Sales Value Using Sort Function


FILTER will return matched entries in the order they occur in the supplied data by default.

If you want to sort results by the sales result in decreasing order, you just need to nest the original FILTER formula within the SORT function as follows:

=SORT(FILTER(TotalData,Sales>=LARGE(Sales,5)),3,-1)

filter on top n values

In this case, FILTER passes the results to the SORT function as an array parameter. The sort index is set to 3(sort results based on Sales values),while the sort order is set to -1 (descending order).

Extract Top N Values Using XLOOKUP formula


Users of Microsoft 365 may achieve the same effects by utilizing the new XLOOKUP function:

=XLOOKUP(LARGE($B$2:$B$12, D3), $B$2:$B$12, $A$2:$A$12), $B$2:$A$12)

In this scenario, LARGE returns the kth largest number as the search value straight to XLOOKUP.

filter on top n values

Using the XLOOKUP formula, you may find matches to the top or bottom values.

This syntax is significantly more accessible than the INDEX MATCH formula. However, please remember that XLOOKUP is only accessible in Excel 365. This formula will not function with Excel 2019, Excel 2016, or older versions.

Related Functions


  • 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)…
  • 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 Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
  • Excel Sort function
    The SORT function in Excel sorts the contents of an array or range alphabetically or numerically by columns or rows.The syntax:=SORT(array, [sort index],) …