INDEX MATCH MATCH

L Barry

I'm trying to create a formula that looks up a row and column and pulls in the resulting detail. I've researched through Community and all the various documentation but can't get this to work.

=INDEX(MATCH([Client Name]@row, MATCH([Assigned To Role]@row, {Timeline Team Assignment}, 0)))

 

My Timeline has a bunch of tasks including a column with Client Name and a column with "Assigned to Role"... I want to look up those two fields in my Master Sheet and return the result. The data in the "Assigned to Role" is a header column in my Master Sheet. The "Timeline Team Assignment" in my formula references the entire sheet. Client Name is a row in my Master Sheet. How can I adjust the formula?

 

In the below example, I’m trying to pull the Assigned To field into the sheet. So for the 1st two rows we’d pull in David Schlosser. The third row below would pull in John Doe.  My actual sheet has 14 different possible Roles with separate columns in the Master Table so definitely need to index off of the row and column. 

Master Table

Client Name          Eff Date           Account Manager        Communications Lead   Billing Analyst

ZeniMax                   1/1/2022              Mary R                           David Schlosser            Frank K

ABC Company         6/1/2022              John Doe                        Lisa P                              Maryann

Smartsheet Support had provided me with the below formula but I can't figure out what pieces and parts of my tables should be included in the Ranges.

=INDEX({Table of Names 06128204}, MATCH([Client Name]@row, {Client Name 06128204}, 0), MATCH([Assigned to Role]@row, {Roles 06128204}, 0))

Can anyone help with this ?

BullandKhmer

=INDEX({Assigned To}, MATCH([Assigned To Role]@row, {Timeline Team Assignment},0))

L Barry

That doesn't work because the role changes based on the client.

I need to match on the client and then match the column heading to find the correct contact to pull into my sheet

Genevieve P.

Hi @L Barry

I'd be happy to help you with an INDEX(MATCH(MATCH! 🙂

This is the structure of an INDEX function:

=INDEX(range, row number, column number)

It essentially looks at a range of cells and uses the row and column numbers to return a single cell value. In most INDEX(MATCH formula combinations we ignore the column number part because the first range is simply one column, and we use MATCH to find the row number.

However in your instance, you need to input a MATCH in both the Row place and the Column place of the function to find the correct cross-section and return the right cell, does that make sense?

The first part of the formula will be a range that covers all of the columns needed for your formula. For example, if your sheet has 3 columns with names in that top row, then it would look across all 3 columns.

=INDEX({Table of Names 06128204},

Then the first MATCH is looking to find what row to bring back. In this case, it's looking for the Client name to match across both sheets:

MATCH([Client Name]@row, {Client Name 06128204}, 0),

Then the second match is looking for what column to find data in, now that we know the row. In this case, we need to find the Role that's listed in the TOP ROW of your source sheet. That means the {range} is simply the top row, across all the columns needed (in my example this range would be 3 cells: the top 3 cells of the sheet):

MATCH([Assigned to Role]@row, {Roles 06128204}, 0))

Does that help explain the breakdown?

Cheers,

Genevieve