where “xtable” is the named range E5:F10. Note: this is an array formula and must be entered with control + shift + enter. To parse the input string into an array or letters, we use MID, ROW, LEN and INDIRECT functions like this: LEN returns the length of the input text, which is concatenated to “1:” and handed off to INDIRECT as text. INDIRECT evaluates the text as a row reference, and the ROW function returns an array of numbers to MID: MID then extracts one character for at each starting position and we have: Essentially, we are asking VLOOKUP to find a match for “a”, “b”, and “c” at the same time. For obscure reasons, we need to “dereference” this array in a particular way using both the T and IF functions. After VLOOKUP runs, we have: and TEXTJOIN returns the string “946”.
Output a number
To output a number as final result (instead of a string), add zero. The math operation will coerce the string into a number.
Sum numbers
To sum the numbers together instead of listing them, you can replace TEXTJOIN with SUM like this:
Note: the TEXTJOIN function was introduced via the Office 365 subscription program in 2018.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
