Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value between range & return text | Excel Worksheet Functions | |||
Formula to return the reference of a range of cells | Excel Discussion (Misc queries) | |||
Return A Range of All Cells With Links | Excel Worksheet Functions | |||
lookup and return range of cells | Excel Worksheet Functions | |||
How do I return the highest value in a range of cells | Excel Worksheet Functions |