Index/Match across 3 sheets resulting in #invalid value

Nick Stamatakis

Hi all,

I have lots of experience with Index/Match in Excel and Smartsheet and this one has stumped me and I can't figure out why. Here's the scoop:

• 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 everyone's help in advance!