# formula to search a list for a keyword in another cell, then get a result from another cell

#### brianaclark1967

##### Member
Afternoon all,

Hoping you can help with this one

I want to search a list for a set word. If found I want to pull the value from a specific cell

I've attached a sample sheet as an example

Thanks all

Brian

#### Attachments

• 9.9 KB Views: 6

#### bosco_yip

##### Excel Ninja
Maybe,

In C2, enter formula :

=COUNTIF(E2:E29,B2&"*")

Regards
Bosco

#### brianaclark1967

##### Member
Hi Bosco

Thanks for getting back to me.

I really wanted the result to be in cell G2

If the word C/lock from cell B2 appears in the list E2:E29, I wanted cell G2 to copy the number from cell C2

If the word doesn't appear in the list, return zero instead in cell G2

Regards

Brian

#### bosco_yip

##### Excel Ninja
Hi Bosco

Thanks for getting back to me.

I really wanted the result to be in cell G2

If the word C/lock from cell B2 appears in the list E2:E29, I wanted cell G2 to copy the number from cell C2

If the word doesn't appear in the list, return zero instead in cell G2

Regards

Brian
Then,

In G2 :

=IF(COUNTIF(E2:E29,B2&"*")>0,C2,0)

Regards
Bosco

#### brianaclark1967

##### Member
Then,

In G2 :

=IF(COUNTIF(E2:E29,B2&"*")>0,C2,0)

Regards
Bosco
That's excellent Bosco,

Worked a treat, thank you so much.

Regards,

Brian