Search with a cell to find text and then do a vlookup

[legendary_popsicle]

I am a longtime lurker for work-related questions. I can usually find what I need, but now I finally have a question where I feel the need to register and post.

Unfortunately, I deal with a lot of dirty data at work. What I need is a formula that can look within text and return what the user really meant to type. Then I would like to be able to do a vlookup with that value.

Example:

A1: Crapples
A2: Carrots
A3: /Milk

B1: FORMULA (see below)

C1: Apple ; D1: Fruit
C2: Carrot ; D2: Veggie
C3: Milk ; D3: Dairy

I want there to be a formula to look in the range C1:C3 and see if any of those words are in the cell A1. If it is, perform a vlookup in the C1:D3 using the value that was found in the cell.

I've gotten to the point where I have a formula (listed below) that tells me whether or not a word from that range is in the cell, but I can't get to the vlookup portion. Let me know if you guys can help!

=IF(ISNUMBER(LOOKUP(9E+99+307,(SEARCH($C$1:$C$3,A1)))),"Food","Other")

 

[T. Valko]

I am a longtime lurker for work-related questions. I can usually find what I need, but now I finally have a question where I feel the need to register and post.

Unfortunately, I deal with a lot of dirty data at work. What I need is a formula that can look within text and return what the user really meant to type. Then I would like to be able to do a vlookup with that value.

Example:

A1: Crapples
A2: Carrots
A3: /Milk

B1: FORMULA (see below)

C1: Apple ; D1: Fruit
C2: Carrot ; D2: Veggie
C3: Milk ; D3: Dairy

I want there to be a formula to look in the range C1:C3 and see if any of those words are in the cell A1. If it is, perform a vlookup in the C1:D3 using the value that was found in the cell.

I've gotten to the point where I have a formula (listed below) that tells me whether or not a word from that range is in the cell, but I can't get to the vlookup portion. Let me know if you guys can help!

=IF(ISNUMBER(LOOKUP(9E+99+307,(SEARCH($C$1:$C$3,A1)))),"Food","Other")

You're pretty close.

Try this...

=LOOKUP(1E100,SEARCH(C$1:C$3,A1),D$1:D$3)

 

[legendary_popsicle]

You were right, I was so close! The formula worked perfect, thanks!

 

[legendary_popsicle]

Is there a way to make this formula variable so that if I manually add names to the Food Table then I won't have to change the range in the formula?

 

[T. Valko]

Is there a way to make this formula variable so that if I manually add names to the Food Table then I won't have to change the range in the formula?

You can create a dynamic range that refers to the lookup table.

Name: Table (or whatever)
Refers to: =$C$1:INDEX($D:$D,MATCH("zzzzz",$C:$C))

Then the lookup formula becomes:

=LOOKUP(1E100,SEARCH(INDEX(Table,,1),A1),INDEX(Table,,2))

 

[legendary_popsicle]

You can create a dynamic range that refers to the lookup table.

Name: Table (or whatever)
Refers to: =$C$1:INDEX($D:$D,MATCH("zzzzz",$C:$C))

Then the lookup formula becomes:

=LOOKUP(1E100,SEARCH(INDEX(Table,,1),A1),INDEX(Table,,2))

This one doesn't seem to be working...

 

[T. Valko]

This one doesn't seem to be working...

Here's a small sample file that demonstrates this.

ZZZpopsicle.xls 15kb

http://cjoint.com/?AGAdItnsep4

Add new items in column A then add new items to the table. Copy the formula down in column B.

 

[legendary_popsicle]

This one doesn't seem to be working...

Nevermind. I had entered in the Dynamic Range formula wrong. Thanks!

 

[T. Valko]

Nevermind. I had entered in the Dynamic Range formula wrong. Thanks!

Good deal. Thanks for the feedback!