I need Help - Trying to do a VLOOKUP Partial Match

JPSmartSheet
JPSmartSheet ✭✭
edited 02/07/23 in Formulas and Functions

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.


Best Answer

  • JPSmartSheet
    JPSmartSheet ✭✭
    edited 02/07/23 Answer ✓

    @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)

«1

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!