How to Index Match

System

This discussion was created from comments split from: How to Index Match.

melgilkessmith

@Paul Newcome I am attempting a similar formula but I keep getting a return value of 1. Here is my formula.

=INDEX({ReferralReport_Test Range 1}, MATCH(3236, DoctorFacilityId:DoctorFacilityId, 0), MATCH("Jan 2024", {ReferralReport_Test Range 2}, 0))

ReferralReport_Test Range 1 = all of the columns like you stated above 16-19

ReferralReport_Test Range 2 = the 1st row in the other sheet

I have another sheet that has the actual number of referrals seen each month based on the DoctorFacilityId. I'm testing the formula this way and hoping to use it as an expression for Data shuttle so that each time I upload an Excel sheet through Data Shuttle it will update the columns based on matching the DoctorFacilityId.

Paul Newcome

@melgilkessmith I would need to also see your source data.

melgilkessmith

@Paul Newcome it would look like this in Excel

Paul Newcome

@melgilkessmith Ok, but what does the Smartsheet look like, and what exactly are the different {Cross Sheet References} looking at?

melgilkessmith

@Paul Newcome the Smartsheet is up above. It is just the one sheet. I would like for the INDEX/MATCH to happen between the excel file and the sheet via data shuttle. Is that possible with a Data Shuttle expression?

Paul Newcome

I'm not sure I follow. What are your {Cross Sheet References} pointing at?

melgilkessmith

@Paul Newcome I may have explained that poorly. I only have one sheet and one excel. When I upload the excel sheet into data shuttle. I want Data Shuttle to lookup the DoctorFacilityID in the sheet, match it with the ID on the excel and return the corresponding value in the month column at that ID row. I also want data shuttle to add any rows where there are new ID's in the source sheet.

I can't figure out the formula to do that.

Paul Newcome

You shouldn't need any formulas in Data Shuttle for that. Data Shuttle is already programmed to match on a unique ID when you are doing the mapping portion of the workflow build.

mrsci3ntist

Hi @Paul Newcome,

Similar question, but here my extended question is how to make the following into a column formula ? (Note this is a screenshot of a sheet from within Smartsheet's "Project Management Office" template workspace). The issue lies with the [column_index] always matching to the first row which will not work in a column formula.

Thanks for your help!

Paul Newcome

@mrsci3ntist You would need to hard-code "Project Name" and remove the $ from before [Project ID]@row. Otherwise you are looking at a drag-fill situation. Once you have a few rows, Smartsheet does have an auto-fill feature that will pull formulas in even if they are not set as column formulas so long as you have at least two populated rows above and/or below the new row.

mrsci3ntist

@Paul Newcome, I just tested this and you're right Smartsheet does indeed continue the formula for new rows even though they aren't column formulas! I should have kept working away and would've likely spotted that. Thanks!