Vlookup across 3 worksheets

Hi,

I know there are probably a 100 answer's already posted about this but I 
can't get one to match. I have a Vlookup formula that i can get to work 
looking across 2 worksheets but when i add the 3rd it says "too many 
arguments". my existing formula is:

=IF(ISERROR(VLOOKUP($A97,'sheet1'!$A$3:$AR$99,11,FALSE)),VLOOKUP($A97,'sheet2'!$A$4:$AR$98,11,FALSE),VLOOKUP($A97,'sheet1'!$A$3:$AR$99,11,FALSE))

I want to add sheet 3 in there but it returns the error. I'm using 
consistent formatting $'s & the worksheets are uniformly set out. 

Any help would be appreciated, especially understanding the brackets/equation.

Thanks
0
Utf
2/4/2010 7:18:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
749 Views

Similar Articles

[PageSpeed] 34

Try the below which will lookup Sheet1,Sheet2, Sheet3.

If you have more sheets say 5 try changing "1:3" to "1:5"

Please note that this is an array formula. You create array formulas in the 
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to 
enter the formula. If successful in 'Formula Bar' you can notice the curly 
braces at both ends like "{=<formula>}"


=VLOOKUP($A97,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(
INDIRECT("Sheet"&ROW(INDIRECT("1:3"))&"!A:A"),$A97)>0,0)&"!A:B"),2,0)

-- 
Jacob


"Ben" wrote:

> Hi,
> 
> I know there are probably a 100 answer's already posted about this but I 
> can't get one to match. I have a Vlookup formula that i can get to work 
> looking across 2 worksheets but when i add the 3rd it says "too many 
> arguments". my existing formula is:
> 
> =IF(ISERROR(VLOOKUP($A97,'sheet1'!$A$3:$AR$99,11,FALSE)),VLOOKUP($A97,'sheet2'!$A$4:$AR$98,11,FALSE),VLOOKUP($A97,'sheet1'!$A$3:$AR$99,11,FALSE))
> 
> I want to add sheet 3 in there but it returns the error. I'm using 
> consistent formatting $'s & the worksheets are uniformly set out. 
> 
> Any help would be appreciated, especially understanding the brackets/equation.
> 
> Thanks
0
Utf
2/4/2010 7:28:01 AM
Modified to suit your requirement..

array entered
=VLOOKUP($A97,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:3"))&"!A1:AR100"),$A97)>0,0)&"!A1:AR100"),11,0)

OR your formula modified non-array formula

=IF(ISERROR(VLOOKUP($A97,Sheet1!$A$3:$AR$99,11,0)),
IF(ISERROR(VLOOKUP($A97,Sheet2!$A$3:$AR$99,11,0)),
IF(ISERROR(VLOOKUP($A97,Sheet3!$A$3:$AR$99,11,0)),
"NotFound",VLOOKUP($A97,Sheet3!$A$3:$AR$99,11,0)),
VLOOKUP($A97,Sheet2!$A$3:$AR$99,11,0)),
VLOOKUP($A97,Sheet1!$A$3:$AR$99,11,0))



-- 
Jacob


"Jacob Skaria" wrote:

> Try the below which will lookup Sheet1,Sheet2, Sheet3.
> 
> If you have more sheets say 5 try changing "1:3" to "1:5"
> 
> Please note that this is an array formula. You create array formulas in the 
> same way that you create other formulas, except you press CTRL+SHIFT+ENTER to 
> enter the formula. If successful in 'Formula Bar' you can notice the curly 
> braces at both ends like "{=<formula>}"
> 
> 
> =VLOOKUP($A97,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(
> INDIRECT("Sheet"&ROW(INDIRECT("1:3"))&"!A:A"),$A97)>0,0)&"!A:B"),2,0)
> 
> -- 
> Jacob
> 
> 
> "Ben" wrote:
> 
> > Hi,
> > 
> > I know there are probably a 100 answer's already posted about this but I 
> > can't get one to match. I have a Vlookup formula that i can get to work 
> > looking across 2 worksheets but when i add the 3rd it says "too many 
> > arguments". my existing formula is:
> > 
> > =IF(ISERROR(VLOOKUP($A97,'sheet1'!$A$3:$AR$99,11,FALSE)),VLOOKUP($A97,'sheet2'!$A$4:$AR$98,11,FALSE),VLOOKUP($A97,'sheet1'!$A$3:$AR$99,11,FALSE))
> > 
> > I want to add sheet 3 in there but it returns the error. I'm using 
> > consistent formatting $'s & the worksheets are uniformly set out. 
> > 
> > Any help would be appreciated, especially understanding the brackets/equation.
> > 
> > Thanks
0
Utf
2/4/2010 7:40:01 AM
Reply:

Similar Artilces:

Excel format doesn't show in worksheet
I've used previous versions of excel for years- and have always found the color fill and font colors I selected for cells would show up right in the active spreadsheet. In my version of Small Business 2003 it is only showing up in the preview mode. We have another computer with Small Business 2003, and it is working fine, it still shows up in the worksheet... Any idea what settings are amiss on mine? ...

select worksheets are corrupting/freezing
Background situation: I am working with a file that contains around 15 PivotTables, all linked to the same cache. The problem: For about a week I have been having trouble with a few of the worksheets - about 3 different tabs, each contains a chart. The file opens fine, no error messages, but when I click on one of the problematic worksheets and then try to change anything on it - close the field list, move the chart, or even select and move to a different worksheet, Excel begins to freeze, and portions of the screen are frozen to the image of the problematic worksheet. I even tried moving...

Having 3 .set files with different ISV's and custom form modifications
Currently we have 2 seperate SQL instances for 2 different environments. We want to be able to create one environment that has 3 companies, with one .set file with customizations, and alot of ISV's; one company with customizations and 2 ISV's; and create one new company with a plain vanilla GP only 2 ISV's no customizations; We currently have the 2 instances with the first 2 set up that way and now looking to consolidate into one environment with the company dropdowns together. Will this be a pain to manage or is it even possible?? ...

Applying same macro to all worksheets in workbook
HI, I am trying to find an easy way to apply a macro that changes the absolute value of a couple of cells and changes some plot formatting to all sheets in a workbook. All sheets are the same and need the same macro applied to them, I have recorded the macro such that I can click on a worksheet and hit a keyboard command to execute this, but I have to individually select each worksheet and repeat this throughout, how can this be automated? Thanks, Josh Rewrite the macro so it takes a worksheet as an argument. Then write a second macro that iterates your worksheets and passes them one at...

I can't select all my worksheets in Excel
I am unable to select all of my worksheets in my workbook. I know of all the different ways to do it, but is there a setting somewhere that would prevent me from selecting my sheet tabs...other than a protected worksheet? Not that I can think of. And even if the worksheet were protected, you should be able to select any sheet (or group any sheets). Dan Connors wrote: > > I am unable to select all of my worksheets in my workbook. I know of all the > different ways to do it, but is there a setting somewhere that would prevent > me from selecting my sheet tabs...other than a pr...

Protecting a worksheet question
I have a question for Excel 98. I tried to do a simple worksheet protection and provided a password. However, I found that not ALL cell in that particular worksheet was protected. Most of the cell would give you a message telling you the worksheet is protected and you need to unprotect the worksheet to modify the cell content. However, some cells you can still change its content. What are the reasons these things happen? Thank you! Each cell has a locked property that you can toggle. But the "lockedness" doesn't really do anything until the worksheet is protected. Then l...

how do I set up an annual calendar on one worksheet
I need a tempplate to set up on one worksheet an annual calendar with days of the week, months and date numbers. I havae to set up a calendar that has recurring dates e.g. evry 3rd Weds in Feb, 4th Tues in may etc ANy help would be appreciated on all or some of the above start here: http://office.microsoft.com/en-us/results.aspx?Scope=TC&Query=calendar -- Gary''s Student "Ian Dee" wrote: > I need a tempplate to set up on one worksheet an annual calendar with days of > the week, months and date numbers. > I havae to set up a calendar that has recurring ...

401 Worksheet
I tried runing a backdated 401 worksheet to recapture some lost data in HQ from the stores. I needed to go back several years. It was painfully slow and then it froze. Does anyone have suggestions on a better way to syncronize the store data to HQ. I read somewhere that before you run a 401 of such magnitude you should turn off the downloading of journals .. should speed things up for you. Also, suggest you run at the LAST connection of the night so that it has time to finish before another connection occurs. Scott "MAS" wrote: > I tried runing a backdated 401 worksh...

HELP: Outlook downloads 3,4, now 5 copies of each email
I make Outlook 2000 use a Outlook.pst file on a separate hard disk instead of the usual C:\Documents & Settings\ folder. Everything is fine. I also TICK "Leave copy of message on server" so I always have a backup copy. Now when i format my PC and reinstall Windows & Outlook, I point to my D:\Outlook.pst and it starts using it. Then all of a sudden it starts downloading ALL my emails again. If I format my PC once again and reinstall Windows & Outlook, then Outlook starts downloading ALL my emails TWICE (there's 2 copies of each email downloaded, plus the copy I...

Strage function in worksheet {=TABLE(I64,)}
I am attempting to understand what appears to be a custom function in a workbook I was given, author unknown. It seems to be some type of index or look up but their is no help on it or macro defined for it. has anyone used or familiar with this? If you need more information I would be happy to provide it. {=TABLE(I64,)} Thanks, Mark On Thu, 26 Feb 2004 19:30:25 -0600, "mkadon" <mkadon@hotmail.com> wrote: >I am attempting to understand what appears to be a custom function in a >workbook I was given, author unknown. It seems to be some type of index or >look up...

VLookUp and DropDown List in the same cell
Hi everyone, I know that this is possible but I'm not sure how to accomplish this. In column E, I have a list of zip code. In column I, I have the "Priority" category. Priority will be based on the zip code in column E. This will be accomplished by VLookUp formula, where I will have a separate sheet store all the zip codes and the priorities (ie. Priority #1, Priority #2 and so on). The challenge part is that column I "Priority" column needs to be able to use as a Drop Down List as well, in case that the VLookUp formula needs to be overridden for so...

VLOOKUP with partial match
How can I get a match with VLOOKUP("TBS";a1:c15;2) if in the Table_array the data is "TBS XYZ" ? ...

Finding Accounts with no related Contacts CRM 3.0
Hi, I am trying to find all accounts where there are no related contacts but cannot work out the advanced find syntax to do this. I presume I am just missing something obvious. any ideas. Thanks Justin Hi Justin, You are not missing anything. CRM does not support a NOT operator in queries. You can easily search for accounts that do have at least one contact, but the opposite does not work. The only workaround is a report or some kind of server task (service, plug-in, workflow) setting a count attribute in an account whenever a contact is assigned or removed from an account, so that y...

convert month text (MAR) to month number (3)
I am trying to work with a data set that unfortunately has spit out all the dates in a text format - i.e. 03/01/2009 is MAR 3 2009. Is there a way to convert that text date to an actual date format? Any advice or suggestions are greatly appreciated. Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1 Debug.Print CDate("MAR 3 2009") = 3/3/2009 One problem with the CDate function is that it will bomb out on things that can't be evaluated as a date. Therefore you may want to use the IsDate fun...

Links not working #3
The links in my email are not working, not opening a browser and showing any web sites. Can anyone help, What version of Outlook? "Richard" <anonymous@discussions.microsoft.com> wrote in message news:1b8a01c3f98a$e280c9c0$3501280a@phx.gbl... > The links in my email are not working, not opening a > browser and showing any web sites. Can anyone help, Hi, Check the following link : http://support.microsoft.com/default.aspx?scid=KB;EN-US;177054 This applies for both Outlook and Outlook Express. Hope this helps !!! Regards, Sudharson.AN "Richard" <an...

Need help in data copying. #3
Thanks Mr. Dave. It was really helpful Regards Tom -- SMIL ----------------------------------------------------------------------- SMILE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=488 View this thread: http://www.excelforum.com/showthread.php?threadid=25956 ...

Landscape Printing #3
Yes. We have the latest drivers. I installed this printer 3 weeks ago, and downloaded drivers from hp.com. It is network printer installed on W2K server and shared. Used by 15 users all running XP Pro. This problem is only with 2 workstations, but they are same hardware+installed in same way and same time like all workstations. What else can be wrong? ...

CRM 3.0 Exchange Router fills event log with errors
I've installed CRM 3.0 Exchange router according to Microsoft's documentation and the suggestions I found on this mailbox. However, the service is not working. The error message in the Event Viewer is Microsoft.Crm.Tools.ExchangeConnectorService.ExchangeSinkServiceException: Opening the inbox folder of mailbox: crm failed (System.InvalidCastException: No such interface supported at ADODB.RecordClass.Open(Object Source, Object ActiveConnection, ConnectModeEnum Mode, RecordCreateOptionsEnum CreateOptions, RecordOpenOptionsEnum Options, String UserName, String Password) at M...

X-axis labels #3
Given a data set from 1983 to 2000, I would like the x-axis labels to be 1985, 1990, 1995, 2000. I know how to make Excel only show every 5th year, but I don't know how to make it start with 1985 rather than 1983 without deleting the 1983 and 1984 data points. You have posted the same question twice, so I guess you haven't got your answer yet. First, you absolutely do not have to delete your data. Second, we need to be sure what type of chart you are using. Are you using a line chart or an XY chart? I suggest an XY chart. To change the X axis scale for an XY chart, double cl...

Worksheet change item costs
We have a problem with the worksheet change item costs. If we only want to change the cost price from an item we use this worksheet. We put in several items in a worksheet to be transported to the stores by which some of the items new cost prices are not processed in the store. The cost price in the store of the item is not changed eventhough is says that the worksheet has been processed well. (We installed service pack 1.3 about 2 months ago, which should have fixed the problem) Does anyone else have the same experience and found the problem or solution??. Jeff2 Jeff2, Have you confi...

ListCtrl #3
Hi, I have an owner drawn listctrl. I need to be able to split it for scrolling like Excel does.... You know the sort of thing - I want to lock column 1 in place and scroll the rest. I can get close with 2 listctrls but synching the scolling is too messy. And I want to go on to include more split column/rows if possible - assuming I can crack this one of course. Any thoughts.... Thanks Martin I would suggest using the MSFlexGrid ActiveX control which is included with Visual C++. If that doesn't work I would suggest taking alot of CodeProject.com and codeguru.com for some of these c...

Macro dialog box when opening worksheet
Is there anyway to disable the dialog box that warns a user that a macro is attached to a workbook? That is controlled in the user's installation of Excel under the security settings, I am not aware of a way to disable this notification without actually changing the settings on the computer (Tools -> Macros -> Security)... cheers, NeB gafrueh wrote: > Is there anyway to disable the dialog box that warns a user that a macro is attached to a workbook? Hi if this would be possible this security feature would be useless. You cans et the security level to 'Low' (men...

Vlookup with sums
I have the following spreadsheet. 1609719837 M51410 10 Ser - 5/8" x 50 Ft. 2000 1609710593 2058100 5/8 X 100 Platinum 100 1609718669 10TNSGF 10 Ser - 1/2" x 50 Ft 1609719839 M51470 25 Ser - Industrial 3/4" x 75 Ft. 500 Column A is Product Number, Column B is a Description and Column C is the number of pieces we will be producing. I have a table that does a vlookup on the Product Number and returns a unit of measure which I multiple by the number of pieces we will be producing. My question is how can I have one formula at the bottom of column C th...

combining data from several worksheets in a pivot table
I have a workbook with data for sales there is a separate sheet for each month. Every time an agent makes a sale he records it using the date he processed the sale. the sheet then calculates the week number i want to produce a pivot table showing sales by agent for each week however as weeks cross between 2 months i need to be able to include all 12 sheets in the source data for pivot table can this be done. any ideas please Yes - by using "Multiple consolidation ranges" when setting up the table. In my experience this brings more problems than it is worth - because we do not g...

Office XP Service Pack 3 Install Failure
error code 0x8024002D Auto update informed me of an update downloaded - but failed to install, asking for a Office XP Business CD it required. I don't have Business Office software on my computer, so wondering why it's trying to update it. Also, even with install failure of this update, now my long standing excel spreadsheet files won't open; seem to be looking for this update..... Help? leal1928 wrote: > error code 0x8024002D > Auto update informed me of an update downloaded - but failed to > install, asking for a Office XP Business CD it required. I don'...