How do I take information from two sheets and auto populate into a third sheet?
I am trying to set up this smart sheet where it has a entity that someone can select from a drop down menu. Once they clicked any entity I had it V lookup from another sheet and it would auto generate the address for that entity from a different sheet into the cell next to it. I figured out how to do this part.
I am wondering is there a way to do this same function but have it reference two different sheets at the same time. both would have different named entities and different addresses. I don't want to combine then since they are large sheets.
Thanks,
Jeremy
Best Answer
-
Nick Korna ✭✭✭✭✭✭You can do this with a combination of IFERROR & VLOOKUPorIFERROR and INDEX/MATCH.
For VLOOKUP:
=IFERROR(IFERROR(VLOOKUP(Name@row, {1st Range}, 2, false), VLOOKUP(Name@row, {2nd Range}, 2, false)), "Not found")
In this, the ranges are the name & address columns - if you have more than obviously the number in the VLOOKUP would be larger. The nested IFERRORs mean if the name/address can't be found on the 1st sheet, it tries to find it in the 2nd. If it can't be found the "Not found" message is displayed instead.
For INDEX/MATCH:
=IFERROR(IFERROR(INDEX({1st sheet address column}, MATCH(Name@row, {1st sheet name column}, 0)), INDEX({2nd sheet address column}, MATCH(Name@row, {2nd sheet name column}, 0))), "Not found")
The range names should hopefully be pretty self-explanatory there - the results are the same though:
Example output, where A is in sheet 1 and B in sheet 2:
If you've any questions etc. then just post!
Answers
-
Nick Korna ✭✭✭✭✭✭
You can do this with a combination of IFERROR & VLOOKUPorIFERROR and INDEX/MATCH.
For VLOOKUP:
=IFERROR(IFERROR(VLOOKUP(Name@row, {1st Range}, 2, false), VLOOKUP(Name@row, {2nd Range}, 2, false)), "Not found")
In this, the ranges are the name & address columns - if you have more than obviously the number in the VLOOKUP would be larger. The nested IFERRORs mean if the name/address can't be found on the 1st sheet, it tries to find it in the 2nd. If it can't be found the "Not found" message is displayed instead.
For INDEX/MATCH:
=IFERROR(IFERROR(INDEX({1st sheet address column}, MATCH(Name@row, {1st sheet name column}, 0)), INDEX({2nd sheet address column}, MATCH(Name@row, {2nd sheet name column}, 0))), "Not found")
The range names should hopefully be pretty self-explanatory there - the results are the same though:
Example output, where A is in sheet 1 and B in sheet 2:
If you've any questions etc. then just post!
-
Jeremy Oesch ✭✭
-
Nick Korna ✭✭✭✭✭✭
No problem, happy to have helped!
Help Article Resources
Categories
=COUNTIFS(PNS@row, 1) + COUNTIFS(EIP@row, 1) + COUNTIFS(Facilities@row, 1) + COUNTIFS([Patient Care Experience]@row, 1)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109375,"type":"question","name":"Calculate number of days and years from start date","excerpt":"I need to see how long staff have been with us, their tenure. I need this for any given dates, meaning \"today\". I have the start date, how do I get the days from that in years and days? Example: Start date is 06\/01\/2020, today is 08\/23\/2023 so the Tenure should be 3 years, 2 months, 22 days. I would take 3.2","snippet":"I need to see how long staff have been with us, their tenure. I need this for any given dates, meaning \"today\". I have the start date, how do I get the days from that in years and…","categoryID":322,"dateInserted":"2023-08-24T01:42:08+00:00","dateUpdated":null,"dateLastComment":"2023-08-24T15:05:11+00:00","insertUserID":82351,"insertUser":{"userID":82351,"name":"Leann Gibson","url":"https:\/\/community.smartsheet.com\/profile\/Leann%20Gibson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-04\/7e\/c6\/n7ec6db531603daaefab20990ddcfdd53.jpg","dateLastActive":"2023-08-24T17:16:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":82351,"lastUser":{"userID":82351,"name":"Leann Gibson","url":"https:\/\/community.smartsheet.com\/profile\/Leann%20Gibson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-04\/7e\/c6\/n7ec6db531603daaefab20990ddcfdd53.jpg","dateLastActive":"2023-08-24T17:16:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":44,"score":null,"hot":3385732039,"url":"https:\/\/community.smartsheet.com\/discussion\/109375\/calculate-number-of-days-and-years-from-start-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109375\/calculate-number-of-days-and-years-from-start-date","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109375,"commentID":392349,"name":"Re: Calculate number of days and years from start date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392349#Comment_392349","dateInserted":"2023-08-24T15:05:11+00:00","insertUserID":82351,"insertUser":{"userID":82351,"name":"Leann Gibson","url":"https:\/\/community.smartsheet.com\/profile\/Leann%20Gibson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-04\/7e\/c6\/n7ec6db531603daaefab20990ddcfdd53.jpg","dateLastActive":"2023-08-24T17:16:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-24T15:05:21+00:00","dateAnswered":"2023-08-24T15:05:11+00:00","acceptedAnswers":[{"commentID":392349,"body":"
That worked perfectly, thank you!!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109369,"type":"question","name":"INDEXING and Validating","excerpt":"I have a perplexing question, that I am sure someone knows and I am missing here. I am working on a sheet to gather data from a form. However, this form will get quite lengthily and may span multiple years. With that said this is where I am stumped. =INDEX(COLLECT({Test Sheet 1 Range 1}, {Test Sheet 1 Range 1}, <> \"//www.santa-greenland.com/community/discussion/101851/\"),…","snippet":"I have a perplexing question, that I am sure someone knows and I am missing here. I am working on a sheet to gather data from a form. However, this form will get quite lengthily…","categoryID":322,"dateInserted":"2023-08-23T21:21:49+00:00","dateUpdated":null,"dateLastComment":"2023-08-24T14:04:04+00:00","insertUserID":163519,"insertUser":{"userID":163519,"name":"Frank Hammond","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/Frank%20Hammond","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nWVKDFTEFOKBN.png","dateLastActive":"2023-08-24T15:23:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":163506,"lastUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-24T18:14:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":48,"score":null,"hot":3385713353,"url":"https:\/\/community.smartsheet.com\/discussion\/109369\/indexing-and-validating","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109369\/indexing-and-validating","format":"Rich","lastPost":{"discussionID":109369,"commentID":392314,"name":"Re: INDEXING and Validating","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392314#Comment_392314","dateInserted":"2023-08-24T14:04:04+00:00","insertUserID":163506,"insertUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-24T18:14:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-23T21:50:04+00:00","dateAnswered":"2023-08-23T21:43:36+00:00","acceptedAnswers":[{"commentID":392246,"body":"
