VLOOKUP with conditional criteria.
As mentioned in a couple of previous threads, I've created a complicated cross-sheet formula with several different reports. What I'm trying to do now is have a sheet populate whatever information is found in Range 1 if it meets the conditions mentioned.
So, if anything is entered into Range 1 (Outlet) with the condition of "Business Insider", it will populate whatever information found in Range 2, in this case, "Susanna Speier." If any other name is entered in this range further down, it will also automatically be added to my cross sheet. This is the formula I've entered but I think I got lost somewhere along the way because it's unparseable:
=VLOOKUP({2020 CSTE Media Tracking Range 1}, [Bloomberg News], @cell <> "", false)
Best Answer
-
It can work in a contact type column if it is only for overview/informational purposes and not for automations.
To pull multiple names, you are going to want something more along the lines of...
=JOIN(COLLECT({2020 CSTE Media Tracking Contact Column}, {2020 CSTE Media Tracking Outlet Column},[Column3]@row), "; ")
Answers
-
You are going to want something more like this...
=VLOOKUP(Outlet@row, {2020 CSTE Media Tracking Range 1}, ##, false)
You are going to need to update the "##" with a column number where the data you want to pull resides.
-
Thanks for your response. I tried this:
=VLOOKUP(Outlet@row, {2020 CSTE Media Tracking Range 1}, column_4, false)
and
=VLOOKUP(Contact@row, {2020 CSTE Media Tracking Range 2}, column_4, false)
Both returned unparseable. Is there something I'm missing?
-
=VLOOKUP(Contact@row, {2020 CSTE Media Tracking Range 2}, column_4, false)
The bold portion should be a numerical value.
-
So just a 4?
-
It is going to depend on which column within your range you are trying to pull from. The first column (1) will be on the far left and should be the column that you are matching on. From there the next column to the right is 2, then 3, then 4, so on and so forth...
-
After a little bit of research, I found the INDEX/MATCH formula. Would that work better? I've tried this but it came back unparseable.
=INDEX({2020 CSTE Media Tracking Range 1}:[Bloomberg News], MATCH(Contact, {2020 CSTE Media Tracking Range 2})
-
I prefer INDEX/MATCH personally. For that you are going to have 2 separate ranges and it will look something like this...
=INDEX({2020 CSTE Media Tracking Column to Pull From}, MATCH(Outlet@row, {2020 CSTE Media Tracking Column to Match On}, 0))
-
This is what I entered:
=INDEX({2020 CSTE Media Tracking Range 1}, MATCH(Contact@row, {2020 CSTE Media Tracking Range 2}, 0))
I get "#NOMATCH." Hopefully this screenshot will help show what I'm trying to do based on the screenshot above. I might need to add additional cells depending on how many returns it gets but basically, if any cell on my Media Tracking sheet finds a cell with a name for any of these news outlets, it will put their name in column 4 on my Contacts sheet for quick reference.
-
It looks like you are trying to pull the Contact matched to the Outlet based on your most recent screenshot whereas based on your attempted formulas you are trying to pull the Outlet matched to the Contact.
Based on your most recent screenshot, try this...
=INDEX({2020 CSTE Media Tracking Contact Column}, MATCH([Column3]@row, {2020 CSTE Media Tracking Outlet Column}, 0))
-
What exactly are you trying to pull? The Contact or the Outlet?
-
I'm trying to pull the contact.
-
Then try the last formula I provided.
-
That returns as unparseable.
-
Can you copy/paste the formula that is throwing the error directly from your sheet to here?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
