Hi Will
How much do you know about excel? You will need at least to know excel's feature for evaluating formulas, and how to create array formulas:
Quote:
|
(1) the second LOOKUP includes FIND($E$1:$E$10,A1) but on its own this formula just returns #VALUE.
|
FIND()
on its own can't work with arrays, it's just able to look within a single cell for a character. The common use is:
=FIND("Hello","e",1)
It will return 2
Fill Range E1:E10 with characters a to j, now look at the evaluation if you select the cell containing:
=FIND(E1:E10,"e")
You will see =FIND("a","e") the result will be #VALUE but that's not of interest, the point is, the evaluation shows, that just the value of E1 is evaluated. No matter whether you entered the formula normally or as array formula!
To get FIND() working with arrays you will need a function, which got the ability to work with arrays. That's for example LOOKUP(), or MIN():
Enter the formula =MIN(FIND(E1:E10,"e")) and look at the evaluation, you will see something similiar as before, again only E1 is regarded.
Now enter the above formula as arrayformula and the evaluation will show you the values of each cell E1:E10.
LOOKUP() and MIN(), besides others, always will return a single value, but not an array as result!
Quote:
|
(2) what do the {} mean within the CHOOSE formula? I assume these are not to do with an array?
|
CHOOSE() is a special function. Besides the function N() and T() it got the ability to return an array as result. The {} are used in order that CHOOSE returns an array instead of a single value as result.
The whole formula as you posted, is quite sophisticated. It won't work with numeric values, unless you change REPT("z",255) to - for example - 9^9
Cheers