Need a formula that evaluates multiple cells for data

I need a formula that looks in multiple cells for a value, when it finds a value, do a lookup to find the translated value.

I have a sheet that is gathering data in different languages, and we need them all to be translated to English. Below is the snip it of the sheet.

So if the form is in English, it will return the course name in 'Event Name - En' if the form is in Spanish, the 'Event Name - SP" as below. The column below in yellow needs to be an index where it references a table that has all the different languages of the class in one column and the 2nd column will be all the classes in English. The formula would read each of the 4 columns looking for data, when it finds the class name in one of the 4 columns of different languages, it will look up the English version and return it.

Is this possible with =Index or =vlookup?

Tags:

Best Answer

  • markkrebs
    markkrebs Community Champion
    Answer ✓

    so I'd add a Helper column with a column formula of =JOIN([Event Name - EN]@row:[Event Name - SP]@row)

    just to make the index match formula easier. One issue is if someone inserts a column between those columns. If thats a concern we could do a longer formula in the lookup. So the Lookup formula would be

    =index({return value ref},match(Helper@row,{matching column ref},0))

Answers

  • markkrebs
    markkrebs Community Champion

    Yes this is possible with an Index(match()). Vlookup is never recommended bc if a column is moved the return values will be wrong.

    For each row is there only going to be one entry in the 4 columns or could there be multiple cells with text?

  • AnnThompson
    AnnThompson ✭✭✭✭

    To answer your question:

    For each row is there only going to be one entry in the 4 columns or could there be multiple cells with text? Only One Entry

  • markkrebs
    markkrebs Community Champion
    Answer ✓

    so I'd add a Helper column with a column formula of =JOIN([Event Name - EN]@row:[Event Name - SP]@row)

    just to make the index match formula easier. One issue is if someone inserts a column between those columns. If thats a concern we could do a longer formula in the lookup. So the Lookup formula would be

    =index({return value ref},match(Helper@row,{matching column ref},0))

  • AnnThompson
    AnnThompson ✭✭✭✭

    Thank you,this worked perfectly!! Thank you again!

  • AnnThompson
    AnnThompson ✭✭✭✭

    So can I add more language columns in between "Event Name -En" AND "Event Name - SP", will the below formula still work?

    Helper column with a column formula of =JOIN([Event Name - EN]@row:[Event Name - SP]@row)

  • markkrebs
    markkrebs Community Champion

    Yes, it will pick up the new columns in the formula

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!