Trying to use Vlookup and contain with specific word from another sheet

Allen T

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-"

Genevieve P.

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?

edapel

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

Allen T

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-"

Genevieve P.

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

Allen T

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

Genevieve P.

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.

Allen T

Ya there are only one RFE, but right now I did that, it give me Incorrect argument.

Genevieve P.

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?

Allen T

great it works now thanks. I was getting super close, but ya I couldn't figure out the parentheses