excel xlookup - Free Excel Tutorial

February 18, 2022February 19, 2022

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

=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.
B2: This is the input value.
Find: This command specifies the text to be found in the input range.
Replace: It represents the text that is being replaced.
The comma sign (,): is a separator that may separate a list of values.
Parenthesis (): The primary purpose of the parenthesis () symbol is to organize the components.

Let’s See How This Formula Works:

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

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

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

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

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

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

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

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

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

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

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

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

February 12, 2022

Excel XLOOKUP Function

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

What exactly is Excel XLOOKUP formula?

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

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

XLOOKUP syntax Function

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

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

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

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

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

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

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

The XLOOKUP Function’s Advantages

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

INDEX MATCH now uses a single function rather than two.

The XLOOKUP Function’s Drawbacks

Optional arguments may seem complicated to novices.
Can take longer when two ranges are selected and the spreadsheet has an excessive number of cells.
When the lookup and array results are not the same length, an error is returned.
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])….

February 4, 2022February 12, 2022

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