See if a range of cells contains certain text to return the value
 

I am trying to cross reference several sheets of data to be sure that the
proper numbers are pulled back to my main sheet. The only common column that
exists is name, however, some have the name as "Smith, John" and some as
"Smith, John;Smith, Mary" My current if(isna(vlookup) attempt seems to work
for rows where there is only 1 person (no ;Smith, Mary) it does not find
data if there are 2 names. I can't figure out how to match up on the second
name. Any suggestions?

Currently my formula looks like this
=if(isna(vlookup(Mainsheetcolumn1entry!A5,Secondar yworksheetrangeD$2:1$300,6,false)),0,vlookup(Mains heetcolumn1entry!A5,SecondaryworksheetrangeD$2:1$3 00,6,false))

See if a range of cells contains certain text to return the value
 

Try using a wildcard:

"*"&Mainsheetcolumn1entry!A5&"*"

--
Biff
Microsoft Excel MVP


"Eliah" wrote in message
...
I am trying to cross reference several sheets of data to be sure that the
proper numbers are pulled back to my main sheet. The only common column
that
exists is name, however, some have the name as "Smith, John" and some as
"Smith, John;Smith, Mary" My current if(isna(vlookup) attempt seems to
work
for rows where there is only 1 person (no ;Smith, Mary) it does not find
data if there are 2 names. I can't figure out how to match up on the
second
name. Any suggestions?

Currently my formula looks like this
=if(isna(vlookup(Mainsheetcolumn1entry!A5,Secondar yworksheetrangeD$2:1$300,6,false)),0,vlookup(Mains heetcolumn1entry!A5,SecondaryworksheetrangeD$2:1$3 00,6,false))

See if a range of cells contains certain text to return the value
 

Hi,

You can use wildcards with VLOOKUP
*, ?, and ~


--
Thanks,
Shane Devenshire


"Eliah" wrote:

I am trying to cross reference several sheets of data to be sure that the
proper numbers are pulled back to my main sheet. The only common column that
exists is name, however, some have the name as "Smith, John" and some as
"Smith, John;Smith, Mary" My current if(isna(vlookup) attempt seems to work
for rows where there is only 1 person (no ;Smith, Mary) it does not find
data if there are 2 names. I can't figure out how to match up on the second
name. Any suggestions?

Currently my formula looks like this
=if(isna(vlookup(Mainsheetcolumn1entry!A5,Secondar yworksheetrangeD$2:1$300,6,false)),0,vlookup(Mains heetcolumn1entry!A5,SecondaryworksheetrangeD$2:1$3 00,6,false))

See if a range of cells contains certain text to return the value
 

Maybe you could try wildcards on the lookup value

Assume A2 down contains: Smith, John
and col D is the vlookup's lookup col which may contain:
Smith, John;Smith, Mary

Then in say, B2, copied down:
=VLOOKUP("*"&A2&"*",D:L,6,0)

And if you're looking to list possible multiple results from the source
vlookup's lookup col (ie from col D in the example above), its easiest to
apply/use Autofilter Custom. There is a "contains" option in the Custom
Autofilter dialog's droplist under "Show rows whe" (the option's right at
the bottom of the droplist, 2nd last).
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000 Files:362 Subscribers:62
xdemechanik
---
"Eliah" wrote:
I am trying to cross reference several sheets of data to be sure that the
proper numbers are pulled back to my main sheet. The only common column that
exists is name, however, some have the name as "Smith, John" and some as
"Smith, John;Smith, Mary" My current if(isna(vlookup) attempt seems to work
for rows where there is only 1 person (no ;Smith, Mary) it does not find
data if there are 2 names. I can't figure out how to match up on the second
name. Any suggestions?

Currently my formula looks like this
=if(isna(vlookup(Mainsheetcolumn1entry!A5,Secondar yworksheetrangeD$2:1$300,6,false)),0,vlookup(Mains heetcolumn1entry!A5,SecondaryworksheetrangeD$2:1$3 00,6,false))