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?

Find more posts tagged with

Formulas

INDEX

Accepted answers

markkrebs

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))

All comments

markkrebs

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

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

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

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

AnnThompson

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

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.