Find function - use with an array

**jamsta1972** · 04-10-2011, 07:03 AM

Hello forum,

I am trying to use the Find formula to test if a particular value matches one of the elements in a named array.

I have created the array in the Name Manager: "SheetLetters" = {"SU","TR","SX"}.

If I put this formula in a cell, =FIND("SU",SheetLetters,1), it returns 1.
but if I use this formula, FIND("SX",SheetLetters,1), it returns #VALUE!

What I would like is for a number to be returned if the search string matches any of the values stored in the array. Any advice would be much appreciated as always.

thanks

**Ron Coderre** · 04-10-2011, 08:21 AM

Try this:

Please Login or Register  to view this content.

or...for a case-insensitive search:

Please Login or Register  to view this content.

Edited to include the below:
If there are no matches...this formula traps the error:

Please Login or Register  to view this content.

Also...(there seems to be many approaches to this)

Please Login or Register  to view this content.

Does that help?

**jamsta1972** · 04-10-2011, 08:43 AM

Thanks Ron, that works a treat.
I'd like to understand how and why it works though! If you have time to drop me a few lines of explanation that would be great.

**Ron Coderre** · 04-10-2011, 08:45 AM

Which "that" are your referring to?

**jamsta1972** · 04-10-2011, 12:15 PM

Sorry Ron - I'm not being very helpful in helping you to help me.
I meant this one:

=LOOKUP(10^99,SEARCH("sX",SheetLetters))

thanks again.

**Ron Coderre** · 04-10-2011, 03:20 PM

Here's how this formula works:
=LOOKUP(10^99,SEARCH("sX",SheetLetters))

You already know that the SEARCH function returns a number (for matched items) or an error (for non-matched items)

Two of the useful features (bugs?) of the LOOKUP function are:
• if the item to be found is larger than any of the list values
---it returns the last list value of the same type as the item (numeric, in this case)
• it ignores errors

So, in the above formula, we are looking for an extremely large number: 10^99
in a list that will consist of either a number and two errors, like {#VALUE!,1,#VALUE!}
or three errors {#VALUE!,#VALUE!,#VALUE!}

If any of the tests results in a match, the numeric value will be returned.
Otherwise, there will be no numeric values and the LOOKUP function will return an error.

I hope that helps.