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

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

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

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

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

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

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

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

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

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

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.

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

## 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)``

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

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

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.

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.

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

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

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

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.

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

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

Comment:

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

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.

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

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.

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

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

Step2: Click Enter to get returned value.

Comment:

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

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

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)

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

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

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

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

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

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

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

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

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.

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

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

## 3. Find the First Value Greater Than X Using Formula

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

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

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

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

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

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

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

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.

```Function GetLargestValueColumnHeaderbyExcelHow(rowRange As Range) As String
Dim maxVal As Double
Dim maxCol As Integer
Dim currentCell As Range

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

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.

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

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

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

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

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

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

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

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

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.

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.

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}

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:

## 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}

The formula operation process is shown in the figure:

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

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.

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

## 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}

The formula operation process is shown in the figure:

## 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}

The formula operation process is shown in the figure:

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.

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`

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)`
`= (COUNTIF(\$B\$3: B3, B3)-1)*0.01`

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

G3’s formula is as follows:

`=INDEX(\$A\$3:\$A\$8,MATCH(H3,\$C\$3:\$C\$8,0))`

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

Then you can delete the helper column.

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

Expect Result:

## 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"}`

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

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

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

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

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

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

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.

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.

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

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

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])`

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:

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.

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.

Use the following Formula:

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

Note: A1:E9 is array of tables

## Let’s See How This Formula Works:

`= COLUMN(A1:E9)`

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

`= ROW(A1:E9)`

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

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.

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

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.

## 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))`

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

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")`

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<>"")`

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.

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

### 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.
• `B``2`: 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.

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.

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

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.

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

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

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

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

#6 Select one range to be searched

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

#8 let’s see the result.

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

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.

## General Formula

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

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

## 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`

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

`=Sales>=LARGE(Sales,5)`

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

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.

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