Excel FIND in workbook formula

This tutorial will demonstrate how to find a number in a column or workbook in Excel and Google Sheets.

Find a Number in a Column

The MATCH Function is useful when you want to find a number within a array of values.

This example will find 500 in column B.

=MATCH(500,B3:B11, 0)

The position of 500 is the 3rd value in the range and returns 3.

Find Row Number of Value Using ROW, INDEX and MATCH Functions

To find the row number of the value found, we can add the ROW and INDEX functions to the MATCH Function.

=ROW(INDEX(B3:B11,MATCH(987,B3:B11,0)))

ROW and INDEX Functions

The MATCH Function returns 3 as we have seen from the previous example. The INDEX Function will then return the cell reference or value of the array position returned by the MATCH Function. The ROW function will then return the row of that cell reference.

Filter in Excel

We can find a specific number in an array of data by using a Filter in Excel.

1. Click in the Range you want to filter ex B3:B10.
2. In the Ribbon, select Home > Editing > Sort & Filter > Filter.

1. A drop-down list will be added to the first row in your selected range.

1. Click on the drop-down list to get a list of available options.

1. Type 500 in the search bar.

1. Click OK.

1. If your range has more than one value of 500, then both would show.

1. The rows that do not contain the required values are hidden, and the visible row headers are shown in BLUE.
2. To clear the filter, click on the drop-down and then click Clear Filter From “Values”.

To remove the filter entirely from the Range, select Home > Editing > Sort & Filter > Clear in the Ribbon.

Of course, you can apply this (indirect) method inside other formulas.

Just keep in mind that INDIRECT is a volatile function.

This means that it calculates independently to Excel’s formula dependency tree, which in turn can have an impact on performance for larger spreadsheets.

As an example, we’ll take a report that has a Summary tab, and – based on the point of view in the Summary tab – you’ll want to retrieve information from different tabs.

In this example, the Summary tab contains three divisions and we want to retrieve the Invoiced amount from their respective tabs based on the Month selected on cell B4.

The data in the Division tabs are arranged in a simple way where it indicates the Invoiced amount for each month.

This layout is the same for all three tabs: Game Div., Productivity Div., and Utility Div.

Why not use the IF() function

It’s possible to use the IF() function here by setting it up to check if the Division is Game Div. then look in the Game Div tab, and Productivity Div tab if the Division is Productivity Div and so on.

The problem arises when more tabs are added to the report and you’ll have to revise all the nested IF() statements in the summary.

It would be optimal to have one single dynamic formula which you could use no matter how many tabs you have.

Solution 1: VLOOKUP approach using sheet names and cell references

To start simply, let’s write the basic VLOOKUP formula first.

We are also going to assume that Game Div is fixed and the report has just this tab.

Once the formula is set up, we can proceed to make the tab part dynamic as well. (Here is a link to a guide for VLOOKUP functions).

Start with cell C6.

The syntax for VLOOKUP() is:

= VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

• lookup_value: Since you know that you will be looking at the Game Div. tab, this does not need to be an argument. Lookup value is the cell containing the month, cell B4. Since you want to be able to pull this formula down, fix this cell reference to $B$4.
• table_array: Go to Game Div. and highlight the entire table and add a few more rows to include future data (‘Game Div.’!$A$4:$B$24)
• col_index_num: This tells which column to look at. In this case, since we want the second column in the table array area, we use 2.
• range_lookup: Select TRUE for an approximate match or false for an exact match. In this example, we want an exact match.

Cell C6 =VLOOKUP($B$4,’Game Div.’!$A$4:$B$24,2,FALSE)

However, when this formula is pulled down to C8, we get the same value since our tab is fixed to Game Div..

In order to make the formula dynamic to account for the different Divisions, the table_array part of the formula should be revised.

The INDIRECT() function comes in handy here.

This function gets the right address from a cell or text reference.  (Here is a link to a guide to using the INDIRECT() function.)

The syntax of the INDIRECT() function is:

= INDIRECT(ref_text,[a1])

To understand how it works, the formula =INDIRECT(“A3”) tells the function to go to cell A3 and returns the value in cell A3, which is “Summary Report”.

In the same way, if you use another cell (E5) containing “A1” and use that cell as the ref_text in the formula

Cell E6 = INDIRECT(E5)

it returns the value in cell A1.

The formula uses cell E5 as the address, which points the formula to cell  A1.

This was our basic VLOOKUP formula:

=VLOOKUP($B$4,’Game Div.’!$A$4:$B$24,2,FALSE)

Where does the INDIRECT function come in?

The first parameter, lookup_value, is fixed, as well as the col_index_num and range_lookup.

Since the table_array changes depending on the Division, the INDIRECT() function will be used here.

Before applying the INDIRECT() function, it’s worthwhile noting that when the tab names contain spaces, such as “Game Div.”, then any cell reference to that tab will need to include single quotation marks, e.g. ‘Game Div.’!.

The tab name can’t be replaced with a cell reference without putting it inside the INDIRECT() function since the INDIRECT() function will translate the tab name as the address for VLOOKUP().

As a first step, wrap the table_array argument of VLOOKUP inside the INDIRECT function.

INDIRECT(“’Game Div’!$A$4:$B$24’”)

The full formula reads:

Cell C6 =VLOOKUP($B$4, INDIRECT(“’Game Div’!$A$4:$B$24’”),2,FALSE)

As the INDIRECT() function is now included in the formula, all that’s left to do is to make the tab names dynamic.

Replace “Game Div.” with a cell reference and make sure to keep the single quotation marks.

The table_array parameter now becomes:

INDIRECT(“’”&B6&”’!$A$4:$B$24”)

The & symbol is used to combine the single quotation marks and the content of cell B6.

The final formula becomes:

Cell C6 =VLOOKUP($B$4, INDIRECT(“’”&B6&”’!$A$4:$B$24”),2,FALSE)

Pull the formula down to C8 and it should now show the correct Invoiced amount values.

Solution 2: INDEX-MATCH approach using table names

This approach involves converting all the data in the Division tabs into Excel data tables.

Click on any data cell in the Division tab.

Press CTRL + T to display the Create Table window.

This will prompt you to specify the area of the data table.

This converts the data to an Excel data table.

To change the formatting of the table, click on any table cell and go to Design > Table Styles to select a scheme you prefer or Clear to revert back to the original.

Specify a Table Name under the Design tab.

Note that spaces are not allowed in Table Names so you might want to replace the spaces with underscores.

Do the same for the other two Divisions such that you have these table names:

Go back to the Summary tab and build the formula using the INDEX-MATCH approach. (Here is a link to a guide to using INDEX() and MATCH() functions.)

The syntax of the INDEX() function is:

= INDEX(array, row_num,[column_num])

• array: This is the area where the answer is.
• row_num: How many rows it has to go down to find the answer.
• column_num: How many columns to the right it needs to go to find the answer.

As before, let’s start simply with the core formula first.

We will assume our only tab is Game Div.

Once the formula works for Game Div., we can expand on it to lookup values across the different tabs.

Start the formula by inputting:

Cell C6 =VLOOKUP($B$4,’Game Div.’!$A$4:$B$24,2,FALSE)

Go to the Game Div. tab and select the Invoiced Amount column.

Instead of displaying a cell reference, this will show now show as Game_Div.[Invoiced Amount].

The second parameter of the INDEX() function is the row_num.

Basically, that’s how many rows it needs to move down to find the answer.

Instead of hardcoding this, the MATCH() function is used to find what row corresponds to the Date selected in the Summary tab and return the position  to the INDEX() function.

The syntax of the MATCH() function is:

Cell C6 =VLOOKUP($B$4,’Game Div.’!$A$4:$B$24,2,FALSE)

The Match formula becomes:

Cell C6 =VLOOKUP($B$4,’Game Div.’!$A$4:$B$24,2,FALSE)

The final formula becomes:

Cell C6 =VLOOKUP($B$4,’Game Div.’!$A$4:$B$24,2,FALSE)

Pulling this formula down to the Utility Div. row will return the same values since they are hard-coded to look inside the Game Div. tab.

To fix this, we will use  the INDIRECT() function to help us get the dynamic tab names.

However, the naming convention in cells B6:B8 is different to that of the table names because the latter uses an underscore instead of the space.

We will need a formula that takes cells B6:B8 and converts them to look exactly like the corresponding table names—that formula needs to replace the space with an underscore.

The SUBSTITUTE () function will help us make this replacement.

As a first step let’s introduce the INDIRECT Function.

1. Add the INDIRECT() function by replacing Game_Div. [Invoiced Amount] with INDIRECT(“Game_Div.[Invoiced Amount]”)

2. Replace Game_Div. with the cell reference B6 and combine it with the table header [Invoiced Amount] using the & symbol.

3. To replace the space with an underscore, use the SUBSTITUTE() function.

The syntax is SUBSTITUTE(text,old_text,new_text,[instance_num]).

• text: The cell you want the substitution to take place.
• old_text: What specific character you want to replace. In this case, it is “ “.
• new_text: What to substitute the old_text with. In this case, it is “_”.
• Instance_num: How many times we want the substitution to take place. This is an optional argument. We can leave it out which means we’d like all instances of “ “ to be replace with “_”.

=SUBSTITUTE(B6,“ ”,”_”)&”[Invoiced Amount]”…

4. The same substitution should be applied to the reference of the MATCH() part. The final formula now becomes:

Cell D6 = INDEX(INDIRECT(SUBSTITUTE(B6,“ ”,”_”)&“[Invoiced Amount]”), MATCH(Summary!$B$4, INDIRECT(SUBSTITUTE(B6,“ ”, “_”&“[Date]”),0))

What is the formula for find in Excel?

How do I search for a value in all sheets in Excel?

How do I find special characters in Excel?