Index and Match between sheets providing Invalid value
Hi All,
I have a master data sheet collating uniform info + delivery addresses. All fields work but for some reason half of delivery details come as #INVALID VALUE - Not sure what is the issue or how to resolve.
=INDEX({Uniform site master Range 1}, MATCH([Site SIN number]@row, {Site Details Range 2}, 0))
Best Answer
-
I have ended up redoing the formula, relinking all the columns and it seems to work! not sure why the other one crashed midway… thanks for help and support @Paul Newcome
=INDEX({ADDRESS}, MATCH([Site name]@row, {Site Details Range 1}, 0))
Answers
-
Hello @Aiva,
What are the column types for:
- Uniform site master Range 1
- Site SIN number
- Site Details Range 2
- The column your lookup formula is in
If your are pulling in data to the wrong column type this might cause it. If you are referencing dates and there are blanks this can also be a source of error.
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
-
Hi @Dan Palenchar ,
All the columns are set as Text / Number.
Formula works to half of the sheet and then goes into #invalidvalue although data is provided in originating sheet (although out of 600 invalid value lines, there are few lines that got addresses in)
I also noticed, that some cells are blank even though there is data provided in originating sheet
Any thoughts?
-
Are you able to provide some screenshots for context?
-
hi, @Paul Newcome
This is the end product where I want addresses to pull through to if this helps.
Essentially, this sheet has got unique site numbers and unique pin number for employees where info is on other sheets and if matching pull the delivery address provided for the site against that employee
-
So it looks like the INDEX/MATCH is working for some columns but not others? Is that correct?
How exactly are you populating the source data for those columns that are coming in with the error?
-
@Paul Newcome that's correct, half of the sheet pulled without issues for this particular column, and then from about row 600 down #invalid value.
The data was filled in via dynamic view (for site number + address) by managers and forms (for uniform sizes from employees). Managers had a sheet via dynamic view to update delivery details for each specific site. We had an exercise where employees had to be allocated to their work locations by managers. Then if that same site is picked up against employee, it should pull delivery data manager provided for that site number (they had to select site name from drop down which makes it unique and avoids errors) - making it a super sheet with all data linked from various data collation exercises
I have also got sheets with site numbers / employee numbers that are from our system as true version of facts and need to match information before they get linked into my end product sheet.
Not sure if I am over complicating this?
-
Is it half of the columns in every row or half of the rows in every column that are throwing the error?
-
I have ended up redoing the formula, relinking all the columns and it seems to work! not sure why the other one crashed midway… thanks for help and support @Paul Newcome
=INDEX({ADDRESS}, MATCH([Site name]@row, {Site Details Range 1}, 0))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!