Integrate lookup list (vlookup?) with if statement

22Wordsmith ✭✭✭
edited 10/05/23 in Formulas and Functions

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?



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


    You can write all of that into one column

    Let me know if this helps! Thanks!

  • 22Wordsmith
    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
    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
    22Wordsmith ✭✭✭

    Thanks anyway. Back to the manual handling.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!