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

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:
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!

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

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
******

Try this A1:A10
a1a11a1df12b1qwe12bccasdasd102aasd1ws


with this E1:E10
wedfqwfghhtr2a1a1asdrrr2ddd


You get
a1a1a1ANOTHER VALUEqwANOTHER VALUEasdasdANOTHER VALUEasd

That is only 2 results correct!
Is it a sophisticated formula?
Bad formula and bad analysis!

Bruno

This is a bad formula (see my previous post).

Try this one, it's simple and more reliable:

=(IF(ISERROR(MATCH(A1,$E$1:$E$10,0)),"Another Value",A1))

Bruno

Many thanks for your help - much appreciated. Just in the process of working through it all...

Oh Bruno,

I didn't expect anything else from you

But your formula is great !!! It doesn't look at parts of a value, as the other formula does, but that's just a detail we don't really care for, isn't it?

Your right, I supposed it was a non-sense to look at parts of a value,
but if you like:

Case 1
====
In A1:A10 we have "abc"
in E1:E10 we have "abc"
=(IF(ISERROR(MATCH(A1,$E$1:$E$10,0)),"XXX",A1))
and you get "abc"

Case 2
====
In A1:A10 we have "abc"
in E1:E10 we have "ZZabcKKK"

{=IF(SUM((IF(ISERROR(FIND(A1,$E$1:$E$10)),0,1)))=1 ,A1,"XXX")}
if you want to get "abc"

{=IF(SUM(IF(ISERROR(FIND(A1,$E$1:$E$10)),0,FIND(A1 ,$E$1:$E$10))),INDEX($E$1:$E$10,MATCH(SUM(IF(ISERR OR(FIND(A1,$E$1:$E$10)),0,FIND(A1,$E$1:$E$10))),(F IND(A1,$E$1:$E$10)))),"XXX")}
if you want to get "ZZabcKKK"

Why don't you help me in simplifying the last one?
It looks so heavy!

Bruno

Bruno,

When Quoting parts of the posts replied to, please don't remove the [/QUOTE] tag from the end of the quoted portion. Doing so breaks the forum formatting and makes it harder for others to see where the quoted content ends and your response begins

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]