Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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,F ALSE)),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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup across 3 worksheets
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,F ALSE)),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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup across 3 worksheets
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,F ALSE)),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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP in two worksheets | Excel Discussion (Misc queries) | |||
vlookup looking at many worksheets | Excel Discussion (Misc queries) | |||
VLOOKUP using two worksheets? | Excel Discussion (Misc queries) | |||
Vlookup in different worksheets | Excel Worksheet Functions | |||
Using vlookup on two worksheets | Excel Worksheet Functions |