Help with index function

Roland2023

I have a separate grid with a 'Mapping Table' (screenshot below) with on the first column the values that can also be found in my final sheet. In the final sheet I also have a column that should show the mapped values.

I'm trying to use the INDEX function to pull in the mapped values, and also show 'Pending' in case there's no mapped value found. I only get stuck on an 'UNPARSEABLE' error when using the formula I got to:

=IFERROR(INDEX[Mapping Table]'!B:B, MATCH([Source Mapping]@row, [Mapping Table]!A:A, 0)), "Pending")

I also tried the below where I set the ranges instead, but not luck:

=IFERROR(INDEX[{Original Values}, MATCH{Mapped Values}, "Pending")

If this has to do with the formatting of the formula being incorrect, could you share the best place to find more info on it? I read through the basic formula page, but didn't spot anything that could cause the issue.

Paul Newcome

Give this a try:

=INDEX({Mapped Values}, MATCH([Source Mapping]@row, {Original Values}, 0))

Roland2023

Also tried this one: =INDEX({Original Values}, MATCH([Source Mapping]@row,{Mapped Values}))

But gave me wrong results.. seems im close lol

SoS | Dan Palenchar

Is the Sheet being referenced by your formula sorted? This can influence this formula:

So you could try:

1. =INDEX({Original Values}, MATCH([Source Mapping]@row,{Mapped Values}, 0))
2. =INDEX({Original Values}, MATCH([Source Mapping]@row,{Mapped Values}, 1))
3. =INDEX({Original Values}, MATCH([Source Mapping]@row,{Mapped Values}, -1))

You could also nest the correct version in an IFERROR() to return "Pending" if there is a #NO MATCH

=IFERROR(INDEX({Original Values}, MATCH([Source Mapping]@row,{Mapped Values}, *{{# goes here}}*)), "Pending")

Dan Palenchar | School of Sheets Solutions Consulting (Smartsheet Aligned Gold Partner)

Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus

Smartsheet Tutorial Videos: schoolofsheets.com/youtube

Roland2023

Thanks! They are not sorted in the mapping table, hereby a screenshot:

When i try all 3 examples you provided, you can see below the results I'm getting for all 3 in the same order you provided:

On the formula field (Report sources) i would expect it to show 'Discovery' as 'LDCIDC' maps to 'Discovery'.

I don't get the logic for it to show 'LWMHBOTV' as in the mapping table, this is a value from the 'Original Source' and not from the column 'Mapped Value'.

I must be overlooking something here!

Paul Newcome

Give this a try:

=INDEX({Mapped Values}, MATCH([Source Mapping]@row, {Original Values}, 0))

Roland2023

That did the trick! Will make a not of the proper order for next round, thanks for the quick help!

Paul Newcome

Happy to help. 👍️

Yes. Proper order would be the range to pull from first and the range to match on second. The INDEX function is what pulls. The MATCH function is what matches and tells the INDEX function which row from the array to pull from.