Explanation for this formula
Hi,
I was searching on-line for some help on the Find formula, and came across this. Would someone be able to explain it?
=LOOKUP(REPT("z",255),CHOOSE({1,2},"ANOTHER VALUE",LOOKUP(1000,FIND($E$1:$E$10,A1),$E$1:$E$10) ))
There were a list of values in cells E1:E10.
There were a list of inputs in A1 downwards.
What was wanted was a formula which gave, for each input in A1 downwards, one of the values in E1:E10 if an input in A1 downwards contained one of those values, and if not then it returned "Another Value".
It works but I can't work out how or why!
For example:-
(1) the second LOOKUP includes FIND($E$1:$E$10,A1) but on its own this formula just returns #VALUE.
(2) what do the {} mean within the CHOOSE formula? I assume these are not to do with an array?
Many thanks,
Will
|