 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2008vlookup maybe
I have a spreadsheet that has data in a column of cells that have both numbers and letters (ABCD 123456) that shoud match data in one of two tabs in the same worksheet. What I'm trying to do is have a forumla look at the information in the first tab match it to the same matching information in the other tabs and depending on which of the two tabs in finds the information in will return a answer. I've been playing with the vlookup and can get a formula to look at one sheet and return the right answer, but I can't get a formula to look at both sheets and return the right answer. Hi, If the matching data is only present in one or the other the 2 sheets, (ie, not both) and if you are using FALSE (or 0) as your 4th argument in your VLOOKUP's, you could try something like this: IF(ISNA(VLOOKUP(1st Sheet Lookup Details),VLOOKUP(2nd Sheet Lookup Details),VLOOKUP(1st Sheet Lookup details)) When using FALSE (or 0) as your 4th argument, VLOOKUP returns a #N/A when its lookup value is not found. Regards - Dave. > I have a spreadsheet that has data in a column of cells that have both > numbers and letters (ABCD 123456) that shoud match data in one of two tabs in [quoted text clipped - 4 lines] > formula to look at one sheet and return the right answer, but I can't get a > formula to look at both sheets and return the right answer. If the value is definitly on one of the two sheets and only on one of the two sheets - this formula has no provision for a value with no match and will return #N/A in that case... =VLOOKUP(A2,IF(LOOKUP(A2,DataSheet!$A$2:$A$30)=A2,DataSheet!$A$2:$B$30,DataSheet2!$A$2:$B$30),2,0) where A2 contains the value to lookup... if lookup finds a matching value in the first column of data in the first data sheet: LOOKUP(A2,DataSheet!$A$2:$A$26)=A2 then the table for lookup would be that sheet: DataSheet!$A$2:$B$26 if not, it will be the second sheet: DataSheet2!$A$2:$B$30 In this example, returning data from the second column when there is an exact match... substitute as necessary. > I have a spreadsheet that has data in a column of cells that have both > numbers and letters (ABCD 123456) that shoud match data in one of two tabs in [quoted text clipped - 4 lines] > formula to look at one sheet and return the right answer, but I can't get a > formula to look at both sheets and return the right answer. LOOKUP requires the lookup_vector be sorted in ascending order. Try this: =VLOOKUP(A1,IF(COUNTIF(Sheet2!A1:A5,A1),Sheet2!A1:B5,Sheet3!A1:B5),2,0)  SignatureBiff Microsoft Excel MVP > If the value is definitly on one of the two sheets and only on one of the > two [quoted text clipped - 27 lines] >> a >> formula to look at both sheets and return the right answer.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.