Help with index function
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.
Best Answer

Give this a try:
=INDEX({Mapped Values}, MATCH([Source Mapping]@row, {Original Values}, 0))
Answers

Also tried this one: =INDEX({Original Values}, MATCH([Source Mapping]@row,{Mapped Values}))
But gave me wrong results.. seems im close lol

Is the Sheet being referenced by your formula sorted? This can influence this formula:
So you could try:
 =INDEX({Original Values}, MATCH([Source Mapping]@row,{Mapped Values}, 0))
 =INDEX({Original Values}, MATCH([Source Mapping]@row,{Mapped Values}, 1))
 =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
👨🏼💻 Dan Palenchar  School of Sheets Solutions Consulting  Smartsheet Aligned Gold Partner
If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!
 🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus
 ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube
PS  If you have a follow up response tag me @SoS  Dan Palenchar so I get notified of your reply!

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!

Give this a try:
=INDEX({Mapped Values}, MATCH([Source Mapping]@row, {Original Values}, 0))

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

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.
Help Article Resources
Categories
Check out the Formula Handbook template!