Find reference name across two different sheets and pull forward to 3rd sheet
I have a reference request sheet that will log Approvals. Once approved, Customer reference name is entered manually. I want to then look for that name across two sheets and when matched, pull in 3 fields of information - name, email, phone.
I started with this formula, which works - but only when searching in 1 of the 2 Reference Libraries (Enterprise only)
=(VLOOKUP([Customer Reference]@row, {Enterprise Reference Library Range 2}, 5, false))
I tried to add in the 2nd reference library using this formula, but it returns error: #UNPARSEABLE
=IF([Sales Team] = Enterprise, (VLOOKUP([Customer Reference]@row, {Enterprise Reference Library Range 2}, 5, true)), (VLOOKUP([Customer Reference]@row, {Provider Reference Library Range 1}, 6, TRUE))
Any help?
Best Answer
-
Hi @jcouncil
A few things to note:
When you're looking for text in a formula, you'll need to use Quotes around the word so the formula knows where the text starts and ends. For example, "Enterprise".
Secondly, I see you have a column reference of [Sales Team] but no row reference. You'll want to add @row after the column name to ensure it looks for "Enterprise" in this current row.
Try:
=IF([Sales Team]@row = "Enterprise", VLOOKUP([Customer Reference]@row, {Enterprise Reference Library Range 2}, 5, true), VLOOKUP([Customer Reference]@row, {Provider Reference Library Range 1}, 6, TRUE))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Hi @jcouncil
A few things to note:
When you're looking for text in a formula, you'll need to use Quotes around the word so the formula knows where the text starts and ends. For example, "Enterprise".
Secondly, I see you have a column reference of [Sales Team] but no row reference. You'll want to add @row after the column name to ensure it looks for "Enterprise" in this current row.
Try:
=IF([Sales Team]@row = "Enterprise", VLOOKUP([Customer Reference]@row, {Enterprise Reference Library Range 2}, 5, true), VLOOKUP([Customer Reference]@row, {Provider Reference Library Range 1}, 6, TRUE))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives