EXCEL 2010 Check if sub string value in cell match with other string, output with vlookup

**antolinz** · 04-15-2015, 02:53 AM

hi all,
can anybody help me.

how to create formula if sub string value in cell match with other string, can output with substring it self

A1
sampletext1

reference B1:B3
sample
bunga
flower

question : if A1 contains reference B1:B3, then output will be "sample"
Its difficult for me because we used an reference, thanks a lot

**:) Sixthsense :)** · 04-15-2015, 03:43 AM

	A	B	C
1	bungadefef	sample	Available In List
2		bunga
3		abc

	C
1	=IF(ISNA(LOOKUP(2,1/SEARCH(B1:B3,A1))),"Not Exist","Available")&" In List"

**antolinz** · 04-15-2015, 08:59 AM

Hi sixsenth,
Great, its work at all
one more question, if I want to change output (=IF(ISNA(LOOKUP(2,1/SEARCH(B1:B3,A1))),"Not Exist","Available")&" In List"
change to word it self, example : "bunga" in C1, is it possible ...

**:) Sixthsense :)** · 04-15-2015, 09:07 AM

Originally Posted by antolinz

change to word it self, example : "bunga" in C1, is it possible ...

Little bit confused about your above statement

Can you please explain a bit more for my better understanding?

**antolinz** · 04-15-2015, 09:22 AM

Hi, hope it can explain
if your formula returns "Not Exist","Available")&" In List", what i want is it return word in reference which is "bunga"

2015-04-15_20-21-38.png

**antolinz** · 04-15-2015, 09:26 AM

if coloum A1 "bungadedef" and reference is (sample,bunga,abc) then C1 returns a word containing in reference, which is "bunga"

**:) Sixthsense :)** · 04-15-2015, 09:28 AM

Originally Posted by antolinz

if your formula returns "Not Exist","Available")&" In List", what i want is it return word in reference which is "bunga"

Thanks for the brief

Here is the revised formula

=IFERROR(LOOKUP(2,1/SEARCH(B1:B3,A1),B1:B3),"Not Available In List")

**antolinz** · 04-15-2015, 09:32 AM

Thanks god, solved
if you dont mind, 1 reputation from me, thanks a lot six...

EXCEL 2010 Check if sub string value in cell match with other string, output with vlookup

LinkBack

Thread Tools

Rate This Thread

Display

EXCEL 2010 Check if sub string value in cell match with other string, output with vlookup

Re: EXCEL 2010 Check if sub string value in cell match with other string, output with vloo

Re: EXCEL 2010 Check if sub string value in cell match with other string, output with vloo

Re: EXCEL 2010 Check if sub string value in cell match with other string, output with vloo

Re: EXCEL 2010 Check if sub string value in cell match with other string, output with vloo

Re: EXCEL 2010 Check if sub string value in cell match with other string, output with vloo

Re: EXCEL 2010 Check if sub string value in cell match with other string, output with vloo

Re: EXCEL 2010 Check if sub string value in cell match with other string, output with vloo

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Trying to match partial string to an array, need to output related cell

[SOLVED] Formula or Macro, match string,from 1 cell to a column,String use dash

Trying to match partial string with another string and give position or cell

[SOLVED] Vlookup, Match (Search or Find) partial string within string in a Cell

How to Check if sub string value in cell match with other string from range of cells

Bookmarks

Bookmarks

Posting Permissions