vlookup 3 sheets
Hi,
I have 3 worksheets and want to combine a vlookup to find the sum of the unique criteria. I can get it to work with 2 spreadsheets but not 3. The formula i am using is: =IF(ISERROR(VLOOKUP($A99,'sheet1 v4'!$A$3:$AR$103,COLUMN(AR99),FALSE)),VLOOKUP($A99 ,'Sheet2 v4'!$A$3:$AR$98,COLUMN(AR99),FALSE),VLOOKUP($A99,' Sheet1 v4'!$A$3:$AR$103,COLUMN(AR99),FALSE)) Example: Sheet 1 Apple 2 Banana 5 Orange 11 Sheet 2 Mango 4 Banana 5 Pear 7 Sheet 3 Apple 2 Grape 1 Summary Sheet Apple 4 Banana 10 Orange 11 Pear 7 Grape 1 I must be missing something simple but can't figure it out, any help would be appreciated |
vlookup 3 sheets
Try it like this....
=SUMIF(Sheet1!A$3:A$103,A99,Sheet1!AR$3:AR$103)+SU MIF(Sheet2!A$3:A$103,A99,Sheet2!AR$3:AR$103)+SUMIF (Sheet3!A$3:A$103,A99,Sheet3!AR$3:AR$103) Use the appropriate sheet names. -- Biff Microsoft Excel MVP "Ben" wrote in message ... Hi, I have 3 worksheets and want to combine a vlookup to find the sum of the unique criteria. I can get it to work with 2 spreadsheets but not 3. The formula i am using is: =IF(ISERROR(VLOOKUP($A99,'sheet1 v4'!$A$3:$AR$103,COLUMN(AR99),FALSE)),VLOOKUP($A99 ,'Sheet2 v4'!$A$3:$AR$98,COLUMN(AR99),FALSE),VLOOKUP($A99,' Sheet1 v4'!$A$3:$AR$103,COLUMN(AR99),FALSE)) Example: Sheet 1 Apple 2 Banana 5 Orange 11 Sheet 2 Mango 4 Banana 5 Pear 7 Sheet 3 Apple 2 Grape 1 Summary Sheet Apple 4 Banana 10 Orange 11 Pear 7 Grape 1 I must be missing something simple but can't figure it out, any help would be appreciated |
All times are GMT +1. The time now is 11:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com