INDEX and MATCH across two sheets: a detailed explanation

Create some references on the 'new' sheet to the 'original' sheet

So, first things first, let's create some References.

Go to the "Companies_Public" Sheet.

Right click any cell, and press "Manage References".

This will bring up a pop-up that says "Sheet Reference Manager". Press the "+ Create" button at the top right.

Under "Search for a data source" on the next screen, type in "Companies_Private". Select that sheet. You'll see a snippet of what the sheet looks like within this page:

Select the "Corp" column by clicking the column here, and then rename the "Sheet reference name" to something simple, like "Corp_Column".

Repeat this step for each column ("Stock_Column", "Website_Column"). Make sure you press "Insert Reference" after each time.

---

Link in Cells from 'original' sheet

You'll want to link in the Cell values for the "Corp" value for each row. The "Corp" value is the Primary value for the row. (for more on Primary values, see this link).

To do that, you can right click the cell, then select "Link from Cell in Other Sheet", then find the applicable cell. You can select a range of cells, so for this exercise, we'll be selecting the Corp column range from the "Companies_Private" sheet:

(please note that this will likely link the first 50 rows of the 'original' sheet - you'll need to delete rows that aren't needed once you're through with this setup, if you want)

This "Corp" value is going to take the role of our "Identifier" for our INDEX MATCH Functions below. For new rows, we'll cover that later. This is taking care of existing rows.

--------

INDEX MATCH Functions!

We're finally here. Here's how to use INDEX MATCH across several sheets.

Now that you have References set up, now you can run the INDEX MATCH function.

Here's how INDEX MATCH works, in a nutshell:

=INDEX([Range of data to be displayed from], MATCH([Identifier], [Range to look for Identifier], [sort option]), [optional column index])

The Identifier is simply something that you can utilize to match a cell value from one sheet to another.

For this Identifier, you should use a cell value that is always unique (otherwise, if there are duplicate values, this formula will take from the first value it can find).

Here's how the INDEX MATCH pair function works:

• Use the first portion of the INDEX formula to set the range of data you want to display.
• Use the MATCH in the second part of the INDEX formula to designate what row to pull the data from.
• The third portion of the INDEX formula is optional. Use this to specify which column to pull the data from if the first portion of the INDEX formula covers multiple columns. For how we've set this up, you won't need to worry about that.

--------

Formulas for the columns

So, for our "Companies_Public" sheet, here's the formulas for the "Stock" and "Website" columns:

Stock column formula:

=INDEX({Stock_Column}, MATCH(Corp@row, {Corp_Column}, 0))

Website column formula

=INDEX({Website_Column}, MATCH(Corp@row, {Corp_Column}, 0))

For each of these, you'll want to add the formula to the top row, right click the cell then select "Convert to column formula". This adds the formula to the entire column.

To break this down, here's how the first formula works:

• Indexes the referenced "Stock_Column" range from the "Companies_Private" sheet (which will ultimately return the value we're looking for)
• Then finds the row number that matches the name from the "Corp" column of the "Companies_Public" sheet by searching through the "Corp_Column" range of the "Companies_Private" sheet.

Same goes for the formula for the "Website" column.

Basically, what this does is matches the name from the "Corp" field of the row, finds the adjacent value for "Stock", and displays the value of "Stock" from the original sheet.

To test this, you can simply add in the names willy-nilly to see if it'll pull up the correct values for the other columns, and it ought to never give you the error "#NO-MATCH" as long as the value exists. Example of testing:

I added "(Public)" to each column to make it clear that this is from the "Companies_Public" sheet.