Vlookup/Index/Contains HELP!!!

LisakayS

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

Genevieve P.

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

James Keuning

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

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.

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