VLOOKUP HELP 03-02-10

I need VLOOKUP to search across worksheets where the names are not the same 
(example) Worksheet1 (A): Doe, John; Worksheet2 (A): DoeJ; Worksheet3 (A) 
DoeJK.  Can I have VLOOKUP find each name across worksheet 2 and 3?  Here's 
what I have: =IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$A$206,1,FALSE)),"no","yes").  
Thanks for any help.
0
Utf
3/2/2010 2:54:09 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
695 Views

Similar Articles

[PageSpeed] 40

=vlookup() expects the table to be on a single sheet--not three different tables
on 3 different sheets.

But if you're just trying to determine what sheet that name is located on, you
could use three formulas like:
=isnumber(match(a2,sheet2!a:a,0))

But that will search for an exact match--just like your =vlookup() formula does.

You could parse the name into the string you want, but I have no idea how you'd
get that extra K to search the last sheet.

But depending on what you're looking for, there may be help.

If you could match on the last name "Doe", would that be ok.  (This is a
terrible idea if you have lots of Smith's or Patel's!)

You can use a formula that includes a wild card like this:

=isnumber(match("Doe"&"*",sheet2!a:a,0))
But it'll find a match for "DoeJ", "DoeJK" and even "Doerayso,JK"

If you want to try, you can get just the last name using a formula that finds
the position of the first comma:

=SEARCH(",",A1&",")
Then subtract 1 to get the last name (without the comma):
=SEARCH(",",A1&",")-1

Then use that in the =match() portion (and add the wildcard character "*", too):
=isnumber(match(left(a1,search(",",a1&",")-1)&"*",sheet2!a:a,0))





Excel Help! wrote:
> 
> I need VLOOKUP to search across worksheets where the names are not the same
> (example) Worksheet1 (A): Doe, John; Worksheet2 (A): DoeJ; Worksheet3 (A)
> DoeJK.  Can I have VLOOKUP find each name across worksheet 2 and 3?  Here's
> what I have: =IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$A$206,1,FALSE)),"no","yes").
> Thanks for any help.

-- 

Dave Peterson
0
Dave
3/2/2010 3:34:32 PM
You will have no luck until you clean up the source data. XL will not do 
fuzzy matches well. You want exact matches for the names. 
-- 
HTH...

Jim Thomlinson


"Excel Help!" wrote:

> I need VLOOKUP to search across worksheets where the names are not the same 
> (example) Worksheet1 (A): Doe, John; Worksheet2 (A): DoeJ; Worksheet3 (A) 
> DoeJK.  Can I have VLOOKUP find each name across worksheet 2 and 3?  Here's 
> what I have: =IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$A$206,1,FALSE)),"no","yes").  
> Thanks for any help.
0
Utf
3/2/2010 3:40:01 PM
Dave,

This works great!  One addtional question, the result is a True or False, 
how do I get yes or no in place of T/F?

"Dave Peterson" wrote:

> =vlookup() expects the table to be on a single sheet--not three different tables
> on 3 different sheets.
> 
> But if you're just trying to determine what sheet that name is located on, you
> could use three formulas like:
> =isnumber(match(a2,sheet2!a:a,0))
> 
> But that will search for an exact match--just like your =vlookup() formula does.
> 
> You could parse the name into the string you want, but I have no idea how you'd
> get that extra K to search the last sheet.
> 
> But depending on what you're looking for, there may be help.
> 
> If you could match on the last name "Doe", would that be ok.  (This is a
> terrible idea if you have lots of Smith's or Patel's!)
> 
> You can use a formula that includes a wild card like this:
> 
> =isnumber(match("Doe"&"*",sheet2!a:a,0))
> But it'll find a match for "DoeJ", "DoeJK" and even "Doerayso,JK"
> 
> If you want to try, you can get just the last name using a formula that finds
> the position of the first comma:
> 
> =SEARCH(",",A1&",")
> Then subtract 1 to get the last name (without the comma):
> =SEARCH(",",A1&",")-1
> 
> Then use that in the =match() portion (and add the wildcard character "*", too):
> =isnumber(match(left(a1,search(",",a1&",")-1)&"*",sheet2!a:a,0))
> 
> 
> 
> 
> 
> Excel Help! wrote:
> > 
> > I need VLOOKUP to search across worksheets where the names are not the same
> > (example) Worksheet1 (A): Doe, John; Worksheet2 (A): DoeJ; Worksheet3 (A)
> > DoeJK.  Can I have VLOOKUP find each name across worksheet 2 and 3?  Here's
> > what I have: =IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$A$206,1,FALSE)),"no","yes").
> > Thanks for any help.
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
3/2/2010 9:33:01 PM
Just add another =if() function:

=if(isnumber(match(...)),"yes","no")



Excel Help! wrote:
> 
> Dave,
> 
> This works great!  One addtional question, the result is a True or False,
> how do I get yes or no in place of T/F?
> 
> "Dave Peterson" wrote:
> 
> > =vlookup() expects the table to be on a single sheet--not three different tables
> > on 3 different sheets.
> >
> > But if you're just trying to determine what sheet that name is located on, you
> > could use three formulas like:
> > =isnumber(match(a2,sheet2!a:a,0))
> >
> > But that will search for an exact match--just like your =vlookup() formula does.
> >
> > You could parse the name into the string you want, but I have no idea how you'd
> > get that extra K to search the last sheet.
> >
> > But depending on what you're looking for, there may be help.
> >
> > If you could match on the last name "Doe", would that be ok.  (This is a
> > terrible idea if you have lots of Smith's or Patel's!)
> >
> > You can use a formula that includes a wild card like this:
> >
> > =isnumber(match("Doe"&"*",sheet2!a:a,0))
> > But it'll find a match for "DoeJ", "DoeJK" and even "Doerayso,JK"
> >
> > If you want to try, you can get just the last name using a formula that finds
> > the position of the first comma:
> >
> > =SEARCH(",",A1&",")
> > Then subtract 1 to get the last name (without the comma):
> > =SEARCH(",",A1&",")-1
> >
> > Then use that in the =match() portion (and add the wildcard character "*", too):
> > =isnumber(match(left(a1,search(",",a1&",")-1)&"*",sheet2!a:a,0))
> >
> >
> >
> >
> >
> > Excel Help! wrote:
> > >
> > > I need VLOOKUP to search across worksheets where the names are not the same
> > > (example) Worksheet1 (A): Doe, John; Worksheet2 (A): DoeJ; Worksheet3 (A)
> > > DoeJK.  Can I have VLOOKUP find each name across worksheet 2 and 3?  Here's
> > > what I have: =IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$A$206,1,FALSE)),"no","yes").
> > > Thanks for any help.
> >
> > --
> >
> > Dave Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
3/2/2010 10:22:51 PM
Works great!  Thanks a lot!

"Dave Peterson" wrote:

> Just add another =if() function:
> 
> =if(isnumber(match(...)),"yes","no")
> 
> 
> 
> Excel Help! wrote:
> > 
> > Dave,
> > 
> > This works great!  One addtional question, the result is a True or False,
> > how do I get yes or no in place of T/F?
> > 
> > "Dave Peterson" wrote:
> > 
> > > =vlookup() expects the table to be on a single sheet--not three different tables
> > > on 3 different sheets.
> > >
> > > But if you're just trying to determine what sheet that name is located on, you
> > > could use three formulas like:
> > > =isnumber(match(a2,sheet2!a:a,0))
> > >
> > > But that will search for an exact match--just like your =vlookup() formula does.
> > >
> > > You could parse the name into the string you want, but I have no idea how you'd
> > > get that extra K to search the last sheet.
> > >
> > > But depending on what you're looking for, there may be help.
> > >
> > > If you could match on the last name "Doe", would that be ok.  (This is a
> > > terrible idea if you have lots of Smith's or Patel's!)
> > >
> > > You can use a formula that includes a wild card like this:
> > >
> > > =isnumber(match("Doe"&"*",sheet2!a:a,0))
> > > But it'll find a match for "DoeJ", "DoeJK" and even "Doerayso,JK"
> > >
> > > If you want to try, you can get just the last name using a formula that finds
> > > the position of the first comma:
> > >
> > > =SEARCH(",",A1&",")
> > > Then subtract 1 to get the last name (without the comma):
> > > =SEARCH(",",A1&",")-1
> > >
> > > Then use that in the =match() portion (and add the wildcard character "*", too):
> > > =isnumber(match(left(a1,search(",",a1&",")-1)&"*",sheet2!a:a,0))
> > >
> > >
> > >
> > >
> > >
> > > Excel Help! wrote:
> > > >
> > > > I need VLOOKUP to search across worksheets where the names are not the same
> > > > (example) Worksheet1 (A): Doe, John; Worksheet2 (A): DoeJ; Worksheet3 (A)
> > > > DoeJK.  Can I have VLOOKUP find each name across worksheet 2 and 3?  Here's
> > > > what I have: =IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$A$206,1,FALSE)),"no","yes").
> > > > Thanks for any help.
> > >
> > > --
> > >
> > > Dave Peterson
> > > .
> > >
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
3/4/2010 9:40:01 PM
Reply:

Similar Artilces:

Cell Reference 01-12-10
What I am looking for is that when I enter a formula (In cell B1) to pick up value in A1. Now I need value from cell A5 in cell B2. Next value I need in B3 is A9. Everytime I have to change the cell values manually in column B. Formula I use : - =IF(OR(AK64<$D$4,AK64=$D$4),H331,0) Now I need value from cell H336 and I manually change H331 to H336 shown below =IF(OR(AK64<$D$4,AK64=$D$4),H336,0) Any way to make this automated. Thanks Ankur Bhateja ankur.bhateja@hotmail.com Instead of =IF(OR(AK64<$D$4,AK64=$D$4),H331,0) you could say =IF(AK64<=$D$4,H331,0) ...

Help! current RMS users advice on choosing partner
As an actual user of the product what is your opinion on a local partner versus another partner farther away. I am really torn because I really like RMS but my local partners in my state (only 2) are not gurus. I am sure they can handle "setting me up" but there expertise is on other POS systems. I seemed to understand more of the potential of RMS for my store then they do! I have only gained that knowledge from my use of Quicksell 2000 and reading answers on this forum. My business is a service oriented business. I have suppliers who only sell to me and not the "m...

outlook 2003 02-18-10
Can not send mail from a distribution list created in Outlook 2003 - why? I get an error message that reads - could not be delivered to the following receive 501 5.5.2 RCPT TO syntax error Can someone help me. "pj jakobsen" <pj jakobsen@discussions.microsoft.com> wrote in message news:A0D15814-B5C5-4153-91EF-F3D36CD3F581@microsoft.com... > Can not send mail from a distribution list created in Outlook 2003 - why? > I get an error message that reads - could not be delivered to the following > receive 501 5.5.2 RCPT TO syntax error > Can someone help me. ...

Record Count 10-02-07
Experts, I'm trying to dynamically stored the record count from a subform into a field on my form. Now I have to look at the record count on the subform and manually entered the count into a field on form. There must be a better way. Please help! In the Control Source property of a text box on the main form: =[SubFormControlName].[Form].[recordset].[RecordCount] Where SubFormControlName is the name of the subform control, Not the name of the form that is the Source Object of the subform control. -- Dave Hargis, Microsoft Access MVP "Shiller" wrote: > Experts, >...

Help Sendobjest
I am hopping some one can help me I have an application in Access 2000 which has been running on XP and XP professional for a very long time every morning this application creates a list of reminders it then creates an E-Mail for each reminder and with a loop it sends all the e-mails one at a time the code is DoCmd.SendObject acSendNoObject, , acFormatTXT, StrFullAddress, , , strMailSubject, strMailMsg, False All of a sudden a couple of months ago I noticed that it was sending the first mail ok with the usual out look message and then it just looped through the rest but did not send...

meeting invites now show up as emails only
As of very recently, Outlook invites sent from my work calendar to my home calendar (both Outlook, although work Outlook is 2003 I think) now only show up as emails, not as calendar invites. Does anyone have an idea why this is happening - it used to work just fine. Fixes? ...

VLOOKUP
Hi All I have the following formula: =IF($D$9="","",VLOOKUP($D$9,List!$A$1:$G$500,7,FALSE)) which works fine. Problem: How do I stop the value *0* displaying, if there is no data in worksheet List! column 7 ? Many thanks George George, =IF($D$9="","",IF(VLOOKUP($D$9,List!$A$1:$G$500,7,FALSE)=0;"";VLOOKUP($D$9,L ist!$A$1:$G$500,7,FALSE)) -- Regards, Auk Ales * Please reply to this newsgroup only * * I will not react on unsolicited e-mails * "George Gee" <george.nomaps@ntlworld.xom> wrote in message news:Otk8AZ4$DHA.1...

MRP not creating planned orders in version 10.0
I'm testing version 10.0 on a test system and using a copy of my live database. Version 8.0 creates a planned PO when QTY Available falls below Order Point Qty. It's not working in 10.0. I've checked all my settings from 8.0 to 10.0 and they all look the same. Is there a setting I'm missing? Thanks, Ken Have you rebuilt your low level codes after the upgrade? -- Tim Foster "KRoy" wrote: > I'm testing version 10.0 on a test system and using a copy of my live > database. Version 8.0 creates a planned PO when QTY Available falls below > Ord...

Please Help: Toolbars in DLL Dialog
Hello, Creating toolbars in an EXE app is a relative piece of cake. However, I am trying to insert a toolbar into a DLL dialog. This is how my DLL dialog works: 1) Contents of a function exporting a dialog AFX_MANAGE_STATE(AfxGetStaticModuleState()); CSvg* dlg; try { dlg = new CSvg(); dlg->Create(IDD_MAIN_FRAME); dlg->ShowWindow(SW_SHOW); } catch (CSvg *dlg) { dlg->PostNcDestroy(); delete dlg; } 2) Use a function in my Dialog class to insert a toolbar: int CSvg::OnCreate(LPCREATESTRUCT lpCreateStruct) { if (CDialog::OnCreate(lpCreateStruct) == -1) return -...

Lookup, vlookup, find, or what
Thanks in advance. Xcel07 on WinXP. I have a list of names in sheet 4, I need to search sheet 1, 2 and 3 to see if each name on sheet4 is located on any of the other 3 sheets. Here are a couple of my attempts so far =LOOKUP(C3,'Friday 930am:Monday 1130am'!C2:C25) =SUMPRODUCT(C2*'Friday 930am:Monday 1130am'!C2:C25) To clarify the above Sheet 1 is named Friday 930am Sheet 2 is anmed Friday 12:30pm Sheet 3 is named Monday 1130am Any suggestions are welcome You can't do that type of 3D referencing. Need to individually look through each sheet =ISNUMBE...

Upgrading #10
I am currently using MS Money 99 and recently purchased Money 2006. I want to upgrade and keep using the same file in the new version of the software. (This way I still have all my financial data in tact.) When I go to open the 99 file or restore the 99 backup in the 2006 version, I get an error message that says it cannont locate the file or that is has been corrupted. I know neither of these are true because I can still use the file in Money 99. I don't think Money 2006 knows how to recognize the 99 file. How do I use my Money 99 file in the 2006 version of the software? M...

Date question 01-14-10
Can anyone help with this function: =IF[Date Parts Ordered Completed]+ [Date Part Completed]+[Date Purchasing Completed],not blank,=Date The goal is that is all three fields have a date in them, then put in today's date in the final field. If one of the dates is missing, then the final date is blank. The only problem is if I open up that form I do not want it to change the date to today. I want it to stay the date of the actual completion. Any help is greatly appreciated. Thanks, P-Chu -- Message posted via http://www.accessmonster.com Try this -- Completion_...

NEED HELP-wrong formatting saved ??
After using word 2007 and saving a word file, it changed the formatting from the previous file (i think it was .wps microsoft works word processor) and now its all messed up.....I think I saved it in some other wrong encoding standard......now, whenever I open this file now half of it is in some weird looking unreadable characters....like this with wha捬屨捦ㅳ尠晡‰汜牴档晜獣‰歜牥楮杮尰扤档慜㍦㔱㔰楜獮獲摩㌱ㄳ〶‷਍灜牡素筽⩜灜獮捥癬ㅬ灜畮牣屭湰瑳牡ㅴ灜楮摮湥㝴〲灜桮湡⁧屻湰硴慴ges! That is what seems to happen once the system breaks its laws from the inside. ......couldnt find online solutions...does anyone have any suggestions or s...

Accpac DOS with Adagio Migration to Dynamics 10
Anybody still do these migrations.... ...

OWA Question #10
Hello, we're running Exchange 2003 and I have the following situation. Several employees in our company decided to start a different branch. Instead of creating a separate domain we just created a new account in AD with a different domain suffix. So let's say they used to have account@companyA.com. But now they have account@companyB.com. They've been able to access email via Outlook just fine but when accessing it via OWA, they are unsuccessful. We've tried logging them in with both companyA.com and companyB.com domains in the login screen. companyA.com just retur...

Drag and drop help
I am trying to get drag and drop working between a few windows in my application. I can get the dragging started, but can't get away from the 'no-entry' mouse cursor. I have read through the MSDN and I have done the following in a CTreeView derived class called TreeCtrlArtifacts: void TreeCtrlArtifacts::OnTvnBegindrag(NMHDR *pNMHDR, LRESULT *pResult) { LPNMTREEVIEW pNMTreeView = reinterpret_cast<LPNMTREEVIEW>(pNMHDR); // TODO: Add your control notification handler code here CUIntArray arrayOfSelected; arrayOfSelected.Add(0); CMainFrame *main = (CMainFrame*) AfxGetMa...

Money 2001 #10
when using the portfolio I entered a new transaction under a new acount and an error message appeared "M Money has encountered a problem and needs to close". The computer then shuts down or locks up. This happens whenever I click on portfolio. This problem has occured twice over the past month. The first time I went back one month and reentered all my transactions before the error accured and was going along fine but then it happened again while entering a transaction under a new account. ...

Recordset Help Needed
I have the below code that works some of the times, but not all. Here is my issue. If there is no records in the table it works the first time. Then when I go to add another record it says "No Current Record". If I close the form and then re-open it, it work. I thought by adding the Set rs = Nothing it may help. It did seem to do anything. Private Sub TRAN_TYPE_DblClick(Cancel As Integer) Dim rs As DAO.Recordset Dim rs1 As DAO.Recordset 'Save any edits If Me.Dirty Then Me.Dirty = False End If 'Check there is an order in the form. If...

Help with creating a formula
Hello! I am trying to create a formula that will sum up certain criteria from one column as it relates to criteria in the same row in another column. For example, I want to sum up all the entries that begin with the letter "H" in column A as it relates to an entry of "2" from column B that is in the same row as the "H" entry from column A. COLUMN A COLUMN B H10 2 G01 2 H09 3 H15 2 H12 2 If there is such a formula...

*CANT log into Windows -please help
Hi, I need step by step instructions on how to use my recovery console CD in order to fix a problem. Here is my prob: A virus/malware scan I did probably messed up my logon file. The antivrus program either removed or quaranteined the virus/trojan that was attached to my logon file. Because when AVG prompted me to reboot after this scan, It stopped at the welcome screen logon part. I click on my user name (only one there) and it says its logging on...But then it says saving settings and nothing else happens. My machine does not reboot or anything. It just stays at this logon sc...

NDR Help
Hi, I hope someone can help on this. A few people have complained they cannot e-mail to us always. They get an NDR. This is very intermittent it seems and not everyone is affected. They have forwarded the NDR they have received. I've attached it below. I can't work out if our mail server has sent that to them or if it is generated by their company e-mail server. This happens mainly with one company but i think it has happened to some others as well - i can't be sure but the one company mainly (or even only). It is odd in that they can e-mail and get through then do it aga...

need basic help setting up a graph
Hello, I am trying to set up a chart of days and times. The X- axis shoul have the days of the week (mon, tues, etc) and the Y - axis should hav various times of the day. I would like to have a line from on specific time on one day to another specific time on the next. So, point would be monday at 10:00 and the next would be tuesday at 1:00 followed by wednesday at 12:00, etc. Any suggestions?? Thanks fo your help Davi -- Message posted from http://www.ExcelForum.com Hi David, See my attachment. Is that the situation you explained? I use formula =IF(B2<0.5,B2+1,B2) in column ...

Move data from column to rows HELP!!!
Hi thanks for taking the time to look at my problem, currently i have column that has thousands of rows of information in it, it looks lik this A 40432 432654 3432 532543 32432 523 53425 532532 532 523 532 111 222 333 666 numbers that go on into mabye the 5000-6000 range what i need to do is have that data moved So it looks like this A | B | C 40432 | 32432 | 532 432654 | 523 | 523 3432 | 52432 | 111 532543 | 532532 | 222 So on and so on, so instead of 1 column with 6000 lines it ...

Crystal Reports help
We are currently using GP 8.0 and Crystal Reports 10. We will be running most of our reports through Crystal because you can do so much more than with the Report Writer. I am looking for a good reference book for Crystal 10 (this is the first version I have ever used) any suggestions? You can get books on Crystal 10 in any large bookseller. One thing you won't get is how to use Crystal with Great Plains. There was some training materials available. You will want to consult the Great Plains SDK for help on the tables to use. JO wrote: > We are currently using GP 8.0 and Crystal R...

Trouble with Expression 02-19-10
This expression counts the number of records where a tick box is YES TxtSoleYes: Count(IIf(txtSole,1,Null)) Why doesn't this work to give me the number of No's TxtSoleYes: Count(IIf(txtSole,0,Null)) Thanks Tony -- Why don't my grey cells communicate with each as fast as they used to? I hate getting old! Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1 Tony - Try Sum instead of Count. Count gives the number of non-nulls, and zero is non-null. -- Daryl S "TonyWilliams via AccessMonster.com&...