Moving data from one sheet to another - nothing works! VLOOKUP, INDEX MATCH, INDEX COLLECT

bedholm

Hi Community! Hoping one of you might know what I'm doing wrong here. I'm trying to bring data from my source sheet (a list of requests) into a target sheet (list of placements).

I've created a reference ID that exists in both sheets (KP Placement ID) that is an auto-generated number in the source sheet. It's manually entered in the target sheet. My hope is to use this reference ID to link the sheets so that I can bring other information from the source sheet into the target sheet. I started with the "School Contact" which is a text field, however EVERYTHING I TRY tells me "no match" - even though I can clearly see that there is a match. I've tried VLOOKUP, INDEXMATCH and INDEX COLLECT. Nothing works. Please help!

Formulas I’ve tried:

=VLOOKUP([KP Placement ID]@row, {2024 Placement Request Tracker Range 1}, 16, false)

=INDEX({School Contact}, MATCH([KP Placement ID]@row, {School Contact}, 0))

=INDEX(COLLECT({School Contact}, {2024 Placement Request Tracker Range 3}, [KP Placement ID]@row), 1) - results in "Invalid Ref"

I also tried using the Student Name as the identifier, after verifying that the name was spelled the same in both places.

Source sheet_requests.PNG

Target sheet.PNG

bedholm

Just wanted to come back and share that I was able to figure it out with support from the Smartsheet Technical Support Team. Here was a summary of the solution:

"During today's session we attempted to find out why you were receiving error messages on your INDEX/MATCH formula. First we took a look at the #INVALID REF error, where we found the cross-sheet references in the formula were not correct. We removed the incorrect cross sheet references and added the correct ones, but then were receiving a #NO MATCH error. We double checked that the match value was present on both sheets, and I was able to confirm the formula was setup correctly.

We tried reentering the formula just to ensure there was not a syntax issue, but this still did not resolve it. To test further, we created a new sheet where we input the KP ID, and created the INDEX/MATCH formula. The formula worked on this sheet, which told me there was a discrepancy between the KP ID value on the Request Tracker sheet and the KP ID value on the Roster sheet.

To fix this, we changed the formula in the Request # column which was pulling over the KP ID. We added the VALUE function, which made the formula look like this:

=VALUE([KP ID]@row)

We then changed the INDEX/MATCH formula to reference the Request # column instead of the KP ID column, and the formula worked correctly."

Paul Newcome

The second cross sheet reference in the INDEX/MATCH should be a different range from the first. It should be the range you want to match on, but you have it set as the same range as the first (which is the range you want to pull over).

Genevieve P.

Hi @bedholm

I see you marked Paul's response as not answering your question - can you clarify what you've tried?

The INDEX(MATCH structure works like this:

=INDEX({Column to Return}, MATCH([Matching Value]@row, {Matching Value column}, 0))

So in your case:

=INDEX({School Contact}, MATCH([KP Placement ID]@row, {KP Placement ID Column}, 0))

I will say that based on your screen captures it looks like one sheet is storing the IDs as numbers (to the right of the cell) but the other sheet is storing them as text values (on the left of the cell). Try adding +"" to the KPD ID in your current sheet to translate it into text:

=INDEX({School Contact}, MATCH([KP Placement ID]@row + "", {KP Placement ID Column}, 0))

Cheers,

Genevieve

bedholm

Thank you both for helping - I am still struggling to get this to work.

I created a new column in the target sheet with the KP Placement ID that is text/number format. I used this column as the 'range" in the formula, as follows:

=INDEX({School Contact}, MATCH([KP Placement ID]@row, {Placement Request}, 0))

I still get the #INVALIDREF

Smartsheet error.PNG

Genevieve P.

Hi @bedholm

This is the correct structure now for INDEX(MATCH! 🙂

Invalid Ref refers to one of your {references}. Can you double check that each are pointing to the correct column?

{School Contact} < click on this then "Edit Reference", ensure that the column with the School Contact is selected

{Placement Request} < do the same for this

bedholm

Just wanted to come back and share that I was able to figure it out with support from the Smartsheet Technical Support Team. Here was a summary of the solution:

"During today's session we attempted to find out why you were receiving error messages on your INDEX/MATCH formula. First we took a look at the #INVALID REF error, where we found the cross-sheet references in the formula were not correct. We removed the incorrect cross sheet references and added the correct ones, but then were receiving a #NO MATCH error. We double checked that the match value was present on both sheets, and I was able to confirm the formula was setup correctly.

We tried reentering the formula just to ensure there was not a syntax issue, but this still did not resolve it. To test further, we created a new sheet where we input the KP ID, and created the INDEX/MATCH formula. The formula worked on this sheet, which told me there was a discrepancy between the KP ID value on the Request Tracker sheet and the KP ID value on the Roster sheet.

To fix this, we changed the formula in the Request # column which was pulling over the KP ID. We added the VALUE function, which made the formula look like this:

=VALUE([KP ID]@row)

We then changed the INDEX/MATCH formula to reference the Request # column instead of the KP ID column, and the formula worked correctly."