Vlookup/Index/Contains HELP!!!

Options

Hello,

I have been trying for several days now to grab a contact name if there is a partial match on an address. I have one sheet that will have the address (sheet1) and I want it to pull the owner contact info from second sheet if the address is contained in the address field of sheet2 into sheet1 in the 'Who?' column.

1st sheet has part of the address and the column that needs the info from sheet2:


Contact sheet (2nd sheet)

The current formula that I am using is in the 1st image.

As you can see that the property addresses will not be an exact match. I thought that maybe the unit number was messing it up but I put that in a separate column. The first part of the address will be included in Range2 in the Test Contact Sheet and there will not be an exact match.

Any and all suggestion are welcomed!

Thank you,

Lisakay

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @LisakayS

    It looks like you just have one of the references identified incorrectly.

    In your CONTAINS function, instead of a {cross sheet range} you'll want to point to the cell in this current sheet: [Property Address]@row

    Try:

    =IFERROR(INDEX(COLLECT({Test Contact Sheet Range 1}, {Test Contact Sheet Range 2}, CONTAINS( [Property Address]@row, @cell)), 1), "None")

    Cheers,

    Genevieve

Answers

  • James Keuning
    James Keuning ✭✭✭✭✭
    Options

    I think you will find your answer here:

    https://community.smartsheet.com/discussion/70471/contains-cross-reference-formula

    I am bouncing along on a bus, so I can't test it, but it seems like it will work for you.

  • LisakayS
    LisakayS ✭✭✭
    Options

    Hey James,

    Thank you for the reference but what I am trying to achieve is slightly different. I will not have multiple occurrences to be returned and the data (address) I have to compare is not an exact match. I am trying to find the name associated with the full address by using a partial address. Each address will have 1 value/owner to return.

    Seems others have had luck trying to achieve this with COLLECT and CONTAINS.

    Thanks again,

    Lisakay

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @LisakayS

    It looks like you just have one of the references identified incorrectly.

    In your CONTAINS function, instead of a {cross sheet range} you'll want to point to the cell in this current sheet: [Property Address]@row

    Try:

    =IFERROR(INDEX(COLLECT({Test Contact Sheet Range 1}, {Test Contact Sheet Range 2}, CONTAINS( [Property Address]@row, @cell)), 1), "None")

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!