INDEX and MATCH across two sheets: a detailed explanation

Paul Newcome

@Debbie C. It would look more like this:

=IF(OR(B = B, C = C, 1)

=IF(OR(index({Sheet A}, match([SheetBQueryColumnName]@row, {Sheet A}, 0)) = [SheetBQueryColumnName]@row, index({Sheet A}, match([SheetBQueryColumnName]@row, {Sheet A}, 0)) = [SheetBQueryColumnName]@row), 1)

Debbie C.

 missing something....can you take a look at

=IF(OR(INDEX({sheet A range}, MATCH([Sheet B]@row, {sheet A range},0))= [Sheet B]@row, INDEX({sheet C range}, MATCH([Sheet B]@row, ({sheet C range},0)),1))

this is unparseable!

Paul Newcome

@Debbie C. Your parenthesis are off. You need to remove the opening parenthesis from before {sheet C range}, add another closing parenthesis after that last zero, and remove one of the closing parenthesis from after the one.

Debbie C.

Ok, thanks Paul. I will try this!

Nick Stamatakis

Hi @Brett Wyrick. Thanks so much for your helpful walkthrough. I'll add @Paul Newcome for visibility, just in case ;-)

I finally got some traction with your method of setting up INDEX MATCH in SmartSheet but I do have a few questions; based on my setup:

• I have 1 sheet (Sheet 1) where I import an xlsx document with a bunch of entries (with a PO Number, among other things); Sheet 1 would be my INDEX range for each of the columns I want to pull into Sheet 3
• I have another sheet (Project Intake Sheet, Sheet 2) where I will enter a PO number against a project (this would be my MATCH range against Sheet 1)
• I have a final sheet (Sheet 3) where I am using my formula (below) to start pulling the information from Sheet 1 (relative to the PO entered in Sheet 2) . Once I get each column populated and matching against the relevant PO, my intent was to create a filter to only display the open action items and develop automations to alert the owner of that line item to take action.

I have created the following column formula and entered it into Sheet 3 to pull things together; here is an example of one column (though it will be the same for the rest except for the references):

=INDEX({ABC Invoice Number_Summary}, MATCH([PO Number]@row, {ABC PO Number_PS}, 0))

In Sheet 3, I have my Primary column as the PO Numbers. I went back into Sheet 1 to enter a known PO Number to ensure my formulas in Sheet 3 are correct. I finally have it working but now, on the line items I know match, I keep getting an '#INVALID VALUE' error. This is the part I need help with. My columns seem to be categorized properly (Text/Number, Date, etc.).

NB: I don't seem to have an issue pulling 2 data elements per from row from Sheet 2 to Sheet 3. The issue lies with pulling info from Sheet 1 into Sheet 3, based on the exact same formula (with the references obviously changing based on the data desired).

Once I get this working, my intent is to create a filter on this sheet (Sheet 3) to weed out the ones that don't need action and only display the line items that need action (There is a Status column I am capturing). From this, I intend on creating an automation based on the owner of this line item to follow up on said action item. If there is a better way to do this, I am open to suggestions but this seems to be the way that makes the most sense to me. I would be updating Sheet 1 on a monthly basis so that I can review what's been updated and what's not.

I appreciate your help in advance!

Nick Stamatakis

@Paul Newcome @Brett Wyrick Bringing this back up to your attention ^ Thank you!

Brett Wyrick

@Nick Stamatakis - I haven't touched Smartsheet in like 2 years; just happened to login today. Hi. Glad my post was helpful.

What you're looking to do is an interesting use case, which I will refer to as The Triple Sheet Index Matchup™.

Possible Solutions:

A. Check Data Types:

• The #INVALID VALUE error often arises if there's a mismatch between the data type expected and the actual data. For example, if you are trying to place a text value into a number column or vice versa.
  • Solution: Ensure that the columns where you're inserting the INDEX MATCH results have the correct data type. Double-check the data types in both Sheet 1 and Sheet 3 to ensure consistency.

B. Check Cross-Sheet References:

• Ensure that the cross-sheet references are set up correctly. Nick mentioned he set them up, but it's worth revisiting them. Sometimes, Smartsheet may lose track of these references, especially if there have been changes in the source sheet.
  • Solution: Recreate the cross-sheet references and ensure that they point to the right columns and cells.

C. Create Unique Identifiers if needed:

• For the INDEX MATCH to work flawlessly, the PO Numbers in Sheet 1 must be unique. If there are duplicate PO Numbers, it could lead to inconsistencies or unexpected behavior.
  • Solution: Ensure that every PO Number in Sheet 1 is unique. If there are duplicates, decide on a system for managing them (like appending an additional unique identifier).

D. Error Handling:

• If certain PO Numbers aren't available in Sheet 1, the INDEX MATCH will give an error. This can be managed by adding a conditional check to handle errors.
  • Formula modification:

=IFERROR(INDEX({ABC Invoice Number_Summary}, MATCH([PO Number]@row, {ABC PO Number_PS}, 0)), "Data Not Found") 

Filters:

• Filters: Once the data issue is sorted, applying filters on Sheet 3 should be straightforward. You can set a filter for the 'Status' column to display only the required statuses.

Off the top of my head, if none of those troubleshooting ideas work, it seems like you'd need an extra "MATCH" in there; one to match up with what's on Sheet 1, maybe? I don't have access to Smartsheet anymore, so I can't test out this theory, but I'd lean on @Paul Newcome or others like @Andrée Starå for the logic behind that.

Note that appears that it's the yearly time for Smartsheet's big "ENGAGE" event, so that's likely why you haven't gotten a response from Paul, who I'm glad to see that even 2 years after me posting this, is still here supporting it strongly. He's a nice dude.

Nick Stamatakis

Wow! Thanks so much @Brett Wyrick for coming out of hibernation to help out!

So, I went through each of your possible solutions and I will give some feedback:

A. Check Data Types: I can confirm that in each of my 3 three sheets involved in this exercise, there is no mismatch in data types across the sheets and columns.

B. Check Cross-Sheet References: Even though they were as desired, I re-created these to ensure they were referencing the right sheets and data.

C. Create Unique Identifiers: I was able to create a unique identifier by using the JOIN function for the PO number and a separate Invoice number. This allowed me to keep the info I need to pull information from 2 sheets for 5 of the 7 columns I need. These fall into the "Two Sheet Shootout" for the Index/Match functionality. For the other 2 columns, I am able to narrow down my "Triple Sheet Index Matchup" problem.

D. Error Handling: Great recommendation and I've added it to my formula. I usually do this, when not in a hurry and not late in the evening but, I've managed to add that in regardless.

@Paul Newcome and @Andrée Starå, based on @Brett Wyrick 's suggestion, can you shed some light on how to include another MATCH into my formula?

Essentially, I have:

• Sheet 1: PO Number (1 to many), Invoice Number (1 to 1 with PO), Unique Identifier (JOIN the PO and Invoice Numbers)
• Sheet 2: This is where (in advance of a project commencing) I have the PO Number information and enter it into my Intake Sheet and I want to pull in the name of the Project and PM assigned to the project
• Sheet 3: I've pulled 5 of 7 columns I need from the import sheet I bring into Smartsheet (Sheet 1); the other 2 columns (Project Manager, Project Name) is where I am struggling to pull in the information based on a single PO number (sheet 2) and trying to leverage the work I've done in Sheets 1 and 3.

Is it possible to get the info I need with another nested MATCH function to my formula? Here is an example of the column formula I have in Sheet 3 to pull in info from Sheet 2 based on the info I have available to me:

=IFERROR(INDEX({Project Name_PS}, MATCH([Unique Identifier]@row, {Unique Identifier_Summary}, 0)), "Data Not Found")

Project Name_PS - Located in Sheet 2

Unique Identifier - Located in Sheet 3 (PO and Invoice numbers JOINED)

Unique Identifier_Summary - Located in Sheet 1 (PO and Invoices numbers JOINED)

I am essentially trying to figure out which PM owns what PO numbers based on a monthly import I get from another source and using my Smartsheet info (Project, PM) to do a match against what's outstanding and creating notifications to the PM and the source owner of what needs action.

I am open to ALL suggestions here; I have other use cases for this so if I am able to solve this, I've solves many other problems I've got in my queue :-)

Many thanks!

BethWork

I'm trying to use INDEX to match serial numbers from one sheet to another. If it finds a match then I need it to input the information from a particular cell. I'm having the worst time writing this formula since it's a cross sheet reference. I keep getting INCORRECT ARGUMENT when I write the formula.

=INDEX([Serial #]:[Serial #], MATCH({RKI Pin Pad Serial Numbers Range 1}), 0)

Andrée Starå

Hi @BethWork

I hope you're well and safe!

You have to create the cross-sheet references, and then it should look something like this.

=INDEX({ColumnWithTheValueYouWantToShow}, MATCH(CellThatHaveTheValueToMatch@row,{ColumnWithTheValueToMatchAgainsTheCell}, 0)

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

mvg

@BethWork you shouldn't have to write the formula, just clicking and dragging worked for me

BethWork

@Andrée Starå

Hello! Thank you for the help. I got the formula to work in one cell, but when I then tried to convert it to a column formula it didn't return any results.

=INDEX({RKI Pin Pad Serial Numbers Range 2}, MATCH({RKI Pin Pad Serial Numbers Range 3}, [Serial #]@row), 0)

It now says No Match if I put it in the rest of the column, but I can clearly see matches from the cross sheet reference.

Genevieve P.

Hi @BethWork

You have the {range} and [cell] swapped in your MATCH function 🙂

The 0 should also be inside of the MATCH. Try this:

=INDEX({RKI Pin Pad Serial Numbers Range 2}, MATCH([Serial #]@row, {RKI Pin Pad Serial Numbers Range 3}, 0))

Cheers,

Genevieve

BethWork

Hi @Genevieve P.

When I switch the {range} with [cell] I still have the same problem. the first row works, but none of the others do when I apply it as a column formula. Even though I can clearly see a match from one sheet to the next.

Genevieve P.

Hi @BethWork

A few things to check here then!

Can you confirm that the correct column was selected in the  {RKI Pin Pad Serial Numbers Range 3} range?

If so, then I'd like to see if it's the formula that can't find the match (for example, if the value is seen as a number in one sheet and seen as text in the other).

Try a COUNTIFS down each row to make sure the values are the same:

=COUNTIFS({RKI Pin Pad Serial Numbers Range 3}, [Serial #]@row)