Excel FIND in workbook formula

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

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

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

The Match formula becomes:

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

The final formula becomes:

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

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?