Integrate lookup list (vlookup?) with if statement

22Wordsmith

I have a lookup list of Councils and the towns they cover.

On another sheet, I will be writing something like "the team will visit TOWN A". I want a formula to examine that statement and draw through the corresponding Council from the look up list.

Can I write a formula in Smartsheet to achieve this, noting that there are about 200 towns and 23 Councils in my lookup list?

Also, in the lookup list, should my list of towns be separated by semi-colons or by line breaks?

Julia Bennfors

@22Wordsmith

Your Contact Lookup Sheet should look something like this

It should have all unique 200 towns in each row and paired with a Council.

The Other Sheet (I am calling Visit Log) - I would start by breaking it out like this

You will copy those same 200 Towns in the new sheet. Your Council column will point to the Master Contact List,

In the "Council Member" Column this is my formula.

=INDEX({Master Contact List Sheet: Council Member Column}, MATCH(Town@row, {Master Contact List Sheet: Town Column}, 0))

You can than use that to combine all 3 columns (Text, Town, & Council Member) in the 'Statement" Example

OR

You can write all of that into one column

Let me know if this helps! Thanks!

22Wordsmith

Thanks, Julia. I'm afraid I wasn't clear. I don't want to build the statement "I will visit Town A".

That statement will be entered into the list and I want a search that will read "I will visit Town A", look up the list and flag that Town A is in Local Government Area X.

Paul Newcome

You will need to have a column that houses just the towns. You can use a multi-select dropdown and use a JOIN/COLLECT to pull in all of the appropriate councils, but there is no way to use a formula to search the string, find multiple towns and output the appropriate councils when your towns list is that large.

You MIGHT be able to leverage the API, but even then I only give it a "maybe".

22Wordsmith

Thanks anyway. Back to the manual handling.