Vlookup across 3 worksheets

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

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
 
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)
 
Modified to suit your requirement..

array entere
=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))
 
Back
Top