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?
Best 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
-
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?
-
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
-
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))
-
Thank you,this worked perfectly!! Thank you again!
-
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)
-
Yes, it will pick up the new columns in the formula
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!