Index Match formula = unparseable

Allie12380

Very new user to Smartsheet. Two questions:

1. What formula change needs made to not get error?
2. Would it be better to do index and collect?

Sheet: Fields

Appointments: Appt #, Date, Time, MRN, LLL Language, MRN Language lookup (field populating)

MRN Additional Information: MRN Master, MRN Language, Eff date, Last updated

Relationship: 1 MRN Additional Information to many Appointments

Formula: =INDEX([MRN Language]; MATCH(MRN@row: [MRN Master]; 0))

How I understand this formula is INDEX (what value to return in Appointments table from MRN Additional Information Table) and MATCH (find the MRN at row in Appointments and match to MRN Master in MRN Additional Information.) So when MRN in MRN Additional Information matches an MRN on Appointments return value in MRN Language column.

Referenced stuff for my starting point:

https://help.smartsheet.com/function/match?_gl=1zd0711_gcl_auODUxNDgyNjUwLjE3MjY3Mjg3MzEuNDQwNTgyNzUuMTcyNjcyOTEyMS4xNzI2NzI5MTc4_gaNzkyMDA1MTcxLjE3MjY3Mjg3MzE._ga_ZYH7XNXMZK*MTcyNjkwNzM1NC41LjEuMTcyNjkwODUxNC4zNS4wLjA.

https://community.smartsheet.com/discussion/65302/vlookup-with-another-sheet-with-matching-data

KPH

Hi @Allie12380

Concerning

=INDEX([MRN Language]; MATCH(MRN@row: [MRN Master]; 0))

and revised

=INDEX({MRN Language}, MATCH(MRN@row, {MRN Master}, 0))

The second version is looking better.

The first part of the Index is the data to return and that needs to be a range. [MRN Language] is a column name (one of the reasons for the error in formula 1).

• It should either be {MRN Language} as you changed it to. This will work if the column is in a different sheet to the formula and you have set up a cross sheet reference with that name.
• Or it should be [MRN Language]:[MRN Language] - which references the entire column in the current sheet.

Swapping the semi colons for commas is correct. You don't need semi colons in Smartsheet formula.

The second part of the index identifies which row in the range to return. You are using a MATCH to find this. Which is fine. (You'd use COLLECT if you wanted to find a row that met more than 1 criteria, but as you are just looking for MRN, MATCH is fine).

The first part in the MATCH is the thing to search for - MRN on the current row looks good.

The second part is the range to look in. As with the INDEX this should be either:

• A column in another sheet with a cross sheet reference called {MRN Master}.
• Or a column in the current sheet. In which case the reference would be [MRN Master]:[MRN Master].

This column must be the same size as the one used by the index. The column in the INDEX part being the one to bring back when there is a match in the column in the MATCH part.

The last part of the match (the 0) means find an exact match, which looks correct for your use case.

My initial thought is that you only have one sheet so, you need to change those cross sheet references.

=INDEX([MRN Language]:[MRN Language], MATCH(MRN@row, [MRN Master]:[MRN Master], 0))

If you do have two sheets, then the formula is probably correct, but the cross sheet references might not be the same size/shape - you can recreate those.

Hope this helps.

Allie12380

Changed the ; to , after INDEX and now get #Invalid Ref

=INDEX({MRN Language}, MATCH(MRN@row, {MRN Master}, 0))

KPH

Hi @Allie12380

Concerning

=INDEX([MRN Language]; MATCH(MRN@row: [MRN Master]; 0))

and revised

=INDEX({MRN Language}, MATCH(MRN@row, {MRN Master}, 0))

The second version is looking better.

The first part of the Index is the data to return and that needs to be a range. [MRN Language] is a column name (one of the reasons for the error in formula 1).

• It should either be {MRN Language} as you changed it to. This will work if the column is in a different sheet to the formula and you have set up a cross sheet reference with that name.
• Or it should be [MRN Language]:[MRN Language] - which references the entire column in the current sheet.

Swapping the semi colons for commas is correct. You don't need semi colons in Smartsheet formula.

The second part of the index identifies which row in the range to return. You are using a MATCH to find this. Which is fine. (You'd use COLLECT if you wanted to find a row that met more than 1 criteria, but as you are just looking for MRN, MATCH is fine).

The first part in the MATCH is the thing to search for - MRN on the current row looks good.

The second part is the range to look in. As with the INDEX this should be either:

• A column in another sheet with a cross sheet reference called {MRN Master}.
• Or a column in the current sheet. In which case the reference would be [MRN Master]:[MRN Master].

This column must be the same size as the one used by the index. The column in the INDEX part being the one to bring back when there is a match in the column in the MATCH part.

The last part of the match (the 0) means find an exact match, which looks correct for your use case.

My initial thought is that you only have one sheet so, you need to change those cross sheet references.

=INDEX([MRN Language]:[MRN Language], MATCH(MRN@row, [MRN Master]:[MRN Master], 0))

If you do have two sheets, then the formula is probably correct, but the cross sheet references might not be the same size/shape - you can recreate those.

Hope this helps.