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
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
-
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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!