Trying to use Vlookup and contain with specific word from another sheet
I can't seem this get this to work
=IFERROR(VLOOKUP(Epic@row, {5.0.0_Project Range 1:{5.0.0_Project Range 5}, 5000)),false)
I am trying to cross check another sheet and just lookup for only the specific tag that matches the firm column and with the subfix "RFE-"
Best Answer
-
Hi @Allen T
My apologies! I was missing an extra closing parentheses:
=INDEX(COLLECT({5.0.0_Project Range 5}, {5.0.0_Project Range 1}, [Column9]@row, {5.0.0_Project Range 5}, CONTAINS("RFE", @cell)), 1)
CONTAINS should have 2 to close out, since it's closing INDEX as well.
If this doesn't work, can you take a screen capture of the formula as you've typed it into the cell?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Best advice, do not use VLOOKUP, use Indexmatch. Just do a quick search in Smartsheet U, Community or in the formula handbook and it will show you how to use it.
Main reason not to use VLOOKUP, if anything moved from the column you set it to look to or anyone inserts a column it breaks. Indexmatch says look at the named column, so it can move anywhere. Also much better within Smartsheet.
Hope this helps,
Ed
If this comment helped you, please help me and help others by using the buttons below if you found it💡Insightfulor❤️Awesome!
Hope you have a great day!!
Ed
-
how would i set it to just look for a word that contain just a few letter?
I got this
=INDEX({5.0.0_Project Range 1}, MATCH([Column9]@row, {5.0.0_Project Range 5}))
It works but it is giving me the next blank spot, how I can just search just for "RFE-"
-
Hi @Allen T
It sounds like you have two criteria... that the column with the AOS value has an exact match, and that the value you're bringing back starts with "RFE", is that correct? If so, you can use an INDEX(COLLECT where the Collect function filters down what to bring back. CONTAINS is the function we'll use to see if the cell contains "RFE" along with other data.
Try something like:
=INDEX(COLLECT({Column RFE to Return}, {Firm Column with AOS}, Epic@row, {Column RFE to Return}, CONTAINS("RFE", @cell)), 1)
See this article for more information: Lookup one cell using multiple criteria
If this hasn't helped, it would be useful to see the sheet you're referencing (but block out sensitive data) and to know your current column names.
Thanks!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
yes ,
I got it to work but I can't get it to just show up RFE only.
=INDEX(COLLECT({5.0.0_Project Range 5}, {5.0.0_Project Range 1}, [Column9]@row), CONTAINS("RFE", @cell, 1))
This is where I am at. with this, i would get Incorrect Arugment.
If i have this
=INDEX(COLLECT({5.0.0_Project Range 5}, {5.0.0_Project Range 1}, [Column9]@row), 1))
It will work but will show me the first result
-
Hi @Allen T
The CONTAINS function should still be within the COLLECT function, and we need to list a range before hand.
Try this:
=INDEX(COLLECT({5.0.0_Project Range 5}, {5.0.0_Project Range 1}, [Column9]@row, {5.0.0_Project Range 5}, CONTAINS("RFE", @cell)), 1)
This will still only show you 1 result, but only if it matches RFE in the current column you're looking into, as well as the value in Column 9.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Ya there are only one RFE, but right now I did that, it give me Incorrect argument.
-
Hi @Allen T
My apologies! I was missing an extra closing parentheses:
=INDEX(COLLECT({5.0.0_Project Range 5}, {5.0.0_Project Range 1}, [Column9]@row, {5.0.0_Project Range 5}, CONTAINS("RFE", @cell)), 1)
CONTAINS should have 2 to close out, since it's closing INDEX as well.
If this doesn't work, can you take a screen capture of the formula as you've typed it into the cell?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
great it works now thanks. I was getting super close, but ya I couldn't figure out the parentheses
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!