Lookup or index match for "contains" rather than exact match -- find/search?

Range1:
A1: apple
B1: banana
C1: carrot

Range2:
A2:fruit
B2: fruit
C2: vegetable


A3: Granny Smith Apple



Range 1 and Range 2 are always the same size, but much larger in
reality.

Here's the "formula" I want to create in A4:

If A3 contains 'apple (the first entry in range1), then return 'Fruit'
(the first entry in range2), if A3 contains 'banana' (the 2nd entry of
range1) then return 'Fruit' (the 2nd entry of range2), if a3 contains
'carrot' (the 3rd entry of range1) then return 'vegettable' (the 3rd
entry of range2), otherwise return 'blah'

I don't just want to write this with simple if then statements because
Range1 and Range2 are in practice much larger. This would be easy to
do with Match and Index if A3 exactly matched an entry in Range 1. But
it might not. But A3 only has to CONTAIN one of the entries in Range1.

Hope that makes sense...

Excel@ShoenfeltConsulting.com

Try...

=IF(OR(ISNUMBER(SEARCH(A1:C1,A3))),INDEX(A2:C2,MATCH(TRUE,ISNUMBER(SEARCH
(A1:C1,A3)),0)),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article <1149617181.166660.254950@j55g2000cwa.googlegroups.com>,
"Excel@shoenfeltconsulting.com" <jimhome@starplace.com> wrote:

> Range1:
> A1: apple
> B1: banana
> C1: carrot
>
> Range2:
> A2:fruit
> B2: fruit
> C2: vegetable
>
>
> A3: Granny Smith Apple
>
>
>
> Range 1 and Range 2 are always the same size, but much larger in
> reality.
>
> Here's the "formula" I want to create in A4:
>
> If A3 contains 'apple (the first entry in range1), then return 'Fruit'
> (the first entry in range2), if A3 contains 'banana' (the 2nd entry of
> range1) then return 'Fruit' (the 2nd entry of range2), if a3 contains
> 'carrot' (the 3rd entry of range1) then return 'vegettable' (the 3rd
> entry of range2), otherwise return 'blah'
>
> I don't just want to write this with simple if then statements because
> Range1 and Range2 are in practice much larger. This would be easy to
> do with Match and Index if A3 exactly matched an entry in Range 1. But
> it might not. But A3 only has to CONTAIN one of the entries in Range1.
>
> Hope that makes sense...
>
> Excel@ShoenfeltConsulting.com

I had never thought to use "search" as an array like this. That was
just what I had in mind. Thank you.

Jim


Domenic wrote:
> Try...
>
> =IF(OR(ISNUMBER(SEARCH(A1:C1,A3))),INDEX(A2:C2,MATCH(TRUE,ISNUMBER(SEARCH
> (A1:C1,A3)),0)),"")
>
> ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
>
> Hope this helps!
>
> In article <1149617181.166660.254950@j55g2000cwa.googlegroups.com>,
> "Excel@shoenfeltconsulting.com" <jimhome@starplace.com> wrote:
>
> > Range1:
> > A1: apple
> > B1: banana
> > C1: carrot
> >
> > Range2:
> > A2:fruit
> > B2: fruit
> > C2: vegetable
> >
> >
> > A3: Granny Smith Apple
> >
> >
> >
> > Range 1 and Range 2 are always the same size, but much larger in
> > reality.
> >
> > Here's the "formula" I want to create in A4:
> >
> > If A3 contains 'apple (the first entry in range1), then return 'Fruit'
> > (the first entry in range2), if A3 contains 'banana' (the 2nd entry of
> > range1) then return 'Fruit' (the 2nd entry of range2), if a3 contains
> > 'carrot' (the 3rd entry of range1) then return 'vegettable' (the 3rd
> > entry of range2), otherwise return 'blah'
> >
> > I don't just want to write this with simple if then statements because
> > Range1 and Range2 are in practice much larger. This would be easy to
> > do with Match and Index if A3 exactly matched an entry in Range 1. But
> > it might not. But A3 only has to CONTAIN one of the entries in Range1.
> >
> > Hope that makes sense...
> >
> > Excel@ShoenfeltConsulting.com