Vlookup on a cell with a formula
Hi,
Is it not possible to use a vlookup formula on a cell that contains another formula? I tried and I get "no match".
This is the formula I tried. There is a match in my lookup table for Map ID 2. I have double checked. Thanks for any help!
Best Answer
-
My mistake. The JOIN is outputting text and you are searching for a numerical value.
=INDEX(COLLECT([Outside Lots- Map ID for Zone 1]@row:[Outside Lots- Map ID for Zone 4]@row, [Outside Lots- Map ID for Zone 1]@row:[Outside Lots- Map ID for Zone 4]@row, @cell <> ""), 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Yes, that should work.
Can you describe your process in more detail and maybe share the formula(s), sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
I hope that helps!
Be safe and have a fantastic day!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi!
Does this help?
Formulas below for copy/paste if needed. Image below with a little more explanation. Thanks!
=JOIN([Outside Lots- Map ID for Zone 1]@row:[Outside Lots- Map ID for Zone 4]@row)
=VLOOKUP([Outside Lots- Map ID]@row, {Landscaping- Outside Lots Lookup Table Range 1}, 2, false)
-
I think it has to do with using the JOIN function. Your JOIN function is basically outputting a string of "2blankblankblank", but the other sheet only has a "2". Try replacing the JOIN function with this:
=JOIN(COLLECT([Outside Lots- Map ID for Zone 1]@row:[Outside Lots- Map ID for Zone 4]@row, [Outside Lots- Map ID for Zone 1]@row:[Outside Lots- Map ID for Zone 4]@row, @cell <> ""))
Incorporating a COLLECT function and telling it to only JOIN those cells that are not blank should output "2" instead of "2blankblankblank".
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi!
I copied your formula in and I still get No Match
-
My mistake. The JOIN is outputting text and you are searching for a numerical value.
=INDEX(COLLECT([Outside Lots- Map ID for Zone 1]@row:[Outside Lots- Map ID for Zone 4]@row, [Outside Lots- Map ID for Zone 1]@row:[Outside Lots- Map ID for Zone 4]@row, @cell <> ""), 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi!
I copied your formula in and I still get No Match
-
It worked! Thank you!!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Happy to help!
I saw that Paul answered already!
Let me know if I can help with anything else!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Will do! Thank you both. 😀
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!