Would like to pull information from another sheet
Answers
-
Is the data in the Slot column unique on every row in the worksheet you are wanting to pull from?
-
Hi, Paul,
yes it is
-
Ok. In that case I would suggest an INDEX/MATCH.
=INDEX({Source Sheet Column To Pull From}, MATCH(Slot@row, {Source Sheet Slot Column}, 0))
-
Hello,
I am trying to do this same thing, but reference four different sheets. I've been successful at two, but can't get the third to work. I haven't attempted four since I can't figure out three. Here is what I've done and tried:
Successful single sheet reference:
=IFERROR(INDEX({Sheet A Range 1}, MATCH([ref]@row, {Sheet A Range 2}, 0)), "")
Successful two-sheet reference:
=IFERROR(IFERROR(INDEX({Sheet A Range 1}, MATCH([ref]@row, {Sheet A Range 2}, 0)), INDEX({Sheet B Range 1}, MATCH([ref]@row, {Sheet B Range 2}, 0))), "")
Returns #incorrect argument set:
=IFERROR(IFERROR(INDEX({Sheet A Range 1}, MATCH(ref]@row, {Sheet A Range 2}, 0)), INDEX({Sheet B Range 1}, MATCH([ref]@row, {Sheet B Range 2}, 0)), INDEX({Sheet C Range 1}, MATCH([ref]@row, {Sheet C Range 2}, 0))), "")
Returns #incorrect argument set:
=IFERROR(IFERROR(IFERROR(INDEX({Sheet A Range 1}, MATCH([ref]@row, {Sheet A Range 2}, 0)), INDEX({Sheet B Range 1}, MATCH([ref]@row, {Sheet B Range 2}, 0)), INDEX({Sheet C Range 1}, MATCH([ref]@row, {Sheet C Range 2}, 0)))), "")
I manipulated my formulas based on this one I found on another community thread which has two iferrors and three references:
=iferror(iferror(INDEX({Site Number}, MATCH([Site ID]62, {Site Name}, 0)),INDEX({Site Number ref 2}, MATCH([Site ID ref2]62, {Site Name ref 2}, 0)),INDEX({Site Number ref 3}, MATCH([Site ID ref 3]62, {Site Name ref3}, 0))
I've tried moving things around, but there is something I don't understand in this logic and I can't figure it out.
Thanks!
Amy
-
@Amy G Try working your formula using this line of thinking...
The IFERROR is designed to replace an error. So if there is no match on the first INDEX/MATCH, then you can use the IFERROR to run the second INDEX/MATCH
=INDEX({First Sheet}, MATCH(.............))
=IFERROR(INDEX({First Sheet}, MATCH(.............)), run 2nd sheet formula)
=IFERROR(INDEX({First Sheet}, MATCH(.............)), INDEX({Second Sheet}, MATCH(.............)))
So now we have
=IFERROR(1st sheet formula, 2nd sheet formula)
Now we can say that if there is an error with that one (meaning not located in either of the two sheets, then we want to run the 3rd sheet formula. So we nest the entire first IFERROR into the first portion of the IFERROR statement, then put the 3rd sheet formula in the second portion.
=IFERROR(original formula, 3rd sheet formula)
=IFERROR(IFERROR(INDEX({First Sheet}, MATCH(.............)), INDEX({Second Sheet}, MATCH(.............))), 3rd sheet formula)
Now we do the same exact thing for the 4th sheet formula
=IFERROR(first 3 sheets formula, 4th sheet formula)
=IFERROR(IFERROR(IFERROR(INDEX({First Sheet}, MATCH(.............)), INDEX({Second Sheet}, MATCH(.............))), INDEX({Third Sheet}, MATCH(.............))), 4th sheet formula)
=IFERROR(IFERROR(IFERROR(INDEX({First Sheet}, MATCH(.............)), INDEX({Second Sheet}, MATCH(.............))), INDEX({Third Sheet}, MATCH(.............))), INDEX({Fourth Sheet}, MATCH(.............)))
And you can keep following that to add even more if needed. You would add the IFERROR as the very first function after the initial =. Then you would go to the very end of the existing formula, add a comma, then enter your output for if there is an error and close it out with one more closing parenthesis.
-
Thank you for the prompt response! I didn't realize it was here. I have updated my notification settings to email me when mentioned.
This logic makes sense to me. You explained it very well and I was able to implement it in my sheet.
I tested my understanding by nesting an additional IFERROR to leave the cell blank if no match in any of the sheet references.
Thank you!!
Amy
-
Happy to help. 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives