Cross sheet lookup with criteria for Country & Languages

Brad Grimes

Hope the team can help me here/relatively new and not sure on the function or syntax given all the examples and other posts reviewed. Think this is a vlookup or combo index/match but with conditions against check boxes, text and values. Thanks in advance for the help.

Screen shot attached.

2 sheets

Language:

• This is a core admin sheet of languages, user enters a country, the language and defines it, some hidden columns to insure no dupes for ‘default’ items and conditional formatting applied there etc.

• Not every language that exists is present here, only those that we utilize for the 3 purposes (device, device label and platform)

• Some countries have 1 language, others have multiple

• User selects which language (1 only) is to be used as the default for both device and device label.

• User also selects if that language (1 or multiple) is to be used on the platform

Countries:

Core admin sheet with list of countries supported which include columns for various country details

4 issues to solve on the Countries sheet. Based on the country on the country sheet and corresponding country on language sheet

1. On Country sheet, surface the ‘default’ device label language from the Language sheet (if checked, “OK” and #=1)

2. On Country sheet, surface the ‘default’ device language from the Language sheet (if checked, “OK” and #=1)

3. On Country sheet, surface the list of Platform languages (1 or more) for that country from the Language sheet (if checked in Platform Language column)

4. On Country sheet, surface the # of Platform languages (1 or more) for that country from the Language sheet (checked in Platform Language column)

Thanks

Brad

Cross sheet lookup with criteria for Country & Languages_09Dec2020.png

Kelly Moore

Hi Brad

Give this a whirl. All of these formulas are for your Country sheet. Please remember that you will need to build each of these cross-reference ranges in your Country sheet by clicking the REFERENCE ANOTHER SHEET link in the formula window. You cannot just copy paste from here into your sheet.

[Platform Languages] - I had this set as Multiselect Dropdown

=JOIN(COLLECT({Language List Language}, {Language List Applies to Country}, Country@row, {Language List Platform Language?}, 1), CHAR(10))

(The CHAR(10) puts the line break in between words)

[# of Platform Languages] since it looked like you already had a formula set up on your Language sheet that determined the number, I used an index/match formula to bring the number from the Language sheet. If that wasn't there, see my preferred option as this eliminates a cross-reference formula (assuming [Platform languages] is column type=multiselect dropdown) .

=INDEX({Language List ePL#}, MATCH(Country@row, {Language List Applies to Country}, 0))

or

=COUNTM([Platform Languages]@row)

[Default Device Label Language] I used an Index/Collect. Because an Index needs a row index to know which list position to grab, I determine that using a COUNTIFS with the same criteria as the Collect.

=INDEX(COLLECT({Language List Language}, {Language List Applies to Country}, Country@row, {Language List Default Device Label Language}, 1, {Language List DDLL#check}, @cell = "OK", {Language List DDLL#}, 1), COUNTIFS({Language List Applies to Country}, Country@row, {Language List Default Device Label Language}, 1, {Language List DDLL#check}, @cell = "OK", {Language List DDLL#}, 1))

[Default Device Language] I did not understand how this differed from the formula above, so it's exactly the same formula. I'd be happy to help tweak the formula if there are criteria I missed.

Kelly