Help with Index Match formula with cross sheet reference
Hi! I'm working on what I thought was a simple Index match formula with a cross sheet reference, but the data is not consistently coming back correctly. Some rows work while others dont.
Here is my scenario:
I have Business Areas sheet where the range is from. Intake Process sheet where I want the data to display.
Business Areas sheet has 3 columns: Business Area, Business Owner 1 and Business Owner 2. Sheet B has Business Owner 2 and Business Area. I'm trying to get the Business Owner from Business Area sheet to pull into Intake Process sheet instead of having to list it manually. Range 1 is the Business Owner 2 column from Business Areas sheet. Range 3 is all three columns from Business Areas sheet.
This is the formula: =INDEX({Business Areas Range 1}, MATCH([Business Area]@row, {Business Areas Range 3}, 0))
The Business Areas column is an exact copy of text and formatting from one sheet to the other.
Why does it only work sometimes?
Thanks for your help!
Best Answer
-
Hi @Chris Downes, it looks okay, but there's no reason to have 3 columns in Range 3--that just introduces the possibility of error--so lets start by simplifying that. Range 3 should only be the [Business Area] column from the Business Areas sheet. Also, doublecheck that your Range 1 is pointing to the correct column.
If this doesn't do it, provide some screenshots so we can look at the actual data and see if something stands out.
Let me know if this helps, and please flag this post if I answered your question! Good luck!
Answers
-
Hi @Chris Downes, it looks okay, but there's no reason to have 3 columns in Range 3--that just introduces the possibility of error--so lets start by simplifying that. Range 3 should only be the [Business Area] column from the Business Areas sheet. Also, doublecheck that your Range 1 is pointing to the correct column.
If this doesn't do it, provide some screenshots so we can look at the actual data and see if something stands out.
Let me know if this helps, and please flag this post if I answered your question! Good luck!
-
@Lucas Rayala Thank you! that seemed to fix it!
-
@Chris Downes, glad it worked!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K 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
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!