I need Help - Trying to do a VLOOKUP Partial Match

JPSmartSheet · 02/07/23

Long story short, I can get the partial VLOOKUP match working in Excel fine. Smartsheet is a different story.

Basically, I have two sheets containing data. The first sheet contains the raw data. The raw data sheet is the referenced data used in the Vlookup. I am trying to automate the status of the interfaces in my business by returning the Subject of the E-mail. The problem is that the subject is not static as sometimes it may say 'Interface Failed' or 'Interface Successful', so I want it to return anything that starts with Interface "".

For example, I exported everything from smartsheet to Excel to give you all an example of what I'm trying to do. In excel it works fine. The Vlookup for the partial match is as follows below. I am looking up Cell B52 which is 'cars test' & "*" while referencing the table below. It returns cars test 123 which is what I want it to do in smartsheet.

JPSmartSheet · 02/07/23

@Paul Newcome This worked!!! Also, quick question, is there a way to return the 'Received Date' in a different column. For example, since the criteria was met, it returned cars test 123. I would then like it to return the timestamp of that same row in the raw data sheet column ( Received Date) .

=INDEX(COLLECT({Interface Raw Data Range 1}, {Interface Raw Data Range 1}, CONTAINS([Interface Name]@row, @cell)), 1)

Brent Wilson · 02/07/23

@JPSmartSheet

Is it possible to show us the smartsheet formula that is not working?

JPSmartSheet · 02/07/23

@Brent Wilson

Paul Newcome · 02/07/23

Try an INDEX/COLLECT instead so you can incorporate the CONTAINS function.

=INDEX(COLLECT({Column To Pull From}, {Column To Partial Match On}, CONTAINS([Interface Name]@row, @cell)), 1)

JPSmartSheet · 02/07/23

@Paul Newcome I am getting an incorrect argument set error. If I'm understanding it right, the first data reference is the column I'm pulling from which is the column that 'cars test' is in. The second data point is the column in my raw data spreadsheet, then contains is referencing the exact row that says 'cars test'.

Paul Newcome · 02/07/23

The first range is the range that you want to grab the data to output from. The second range is the range you are looking for the partial match in.

The error is coming from your CONTAINS function. You forgot the second part.

CONTAINS([Interface Name]@row, @cell)

JPSmartSheet · 02/07/23

@Paul Newcome I corrected it, but still receive the error? I highlighted the cell for the second part, but it says @row still.

Paul Newcome · 02/07/23

You should use the "@cell" reference exactly as is.

There is also an issue of where you are pulling vs where you are matching. Are you able to provide screenshots of both sheets and explain exactly what you are pulling from where based on what partially matches?

JPSmartSheet · 02/07/23

@Paul Newcome Sure.

So, the spreadsheet that contains the raw data is the first screenshot. I am looking for anything in Column A (Mail Subject) that starts with 'cars test'. You will see on row 28 there is a cell that contains 'cars test 123'.

The second screenshot is the spreadsheet where I am listing the function and where I want 'cars test 123' to be pulled into column B (Interface Log). So essentially, In a Vlookup fashion, I would use the lookup value as the cell that contains 'cars test', then use the raw data table as the table array and return column 1.

Paul Newcome · 02/07/23

In that case, both ranges would be a cross sheet reference to the Mail Subject column in your raw data sheet. You would then need to update the CONTAINS function as noted above.

JPSmartSheet · 02/07/23

@Paul Newcome This worked!!! Also, quick question, is there a way to return the 'Received Date' in a different column. For example, since the criteria was met, it returned cars test 123. I would then like it to return the timestamp of that same row in the raw data sheet column ( Received Date) .

=INDEX(COLLECT({Interface Raw Data Range 1}, {Interface Raw Data Range 1}, CONTAINS([Interface Name]@row, @cell)), 1)

Paul Newcome · 02/07/23

Happy to help. 👍️

To pull from a different column, you would create a new cross sheet reference to the column you want to pull from and then drop that into the first {Range}. The second {Range} is the one you are matching on, so it would stay the same.

JPSmartSheet · 02/07/23

@Paul Newcome You are awesome!!!!

Paul Newcome · 02/07/23

Happy to help. 👍️

CAS the CSA · 11/20/23

my situation:

datasheet contains | Job number and address (range 3) | visit date (range 2) |

| 000001 | 01/01/2023 |

| 000002 | 01/01/2023 |

| 000001 | 05/01/2023 |

I'm entering what I have below and it's returning #INVALID OPERATION

=INDEX(COLLECT({DataSheet Range 2}, {DataSheet Range 3}, CONTAINS([JobNumber]@row,@CELL)), 1)

have I made a typo? I was under the assumption if the datasheet was sorted decending by date it would produce the largest date for that job number

Paul Newcome · 11/20/23

@CAS the CSA "@cell" should be all lowercase.

I need Help - Trying to do a VLOOKUP Partial Match

Best Answer

Answers

Help Article Resources

Categories