Report

Lookup across whole workbook (muliple sheets) [Solved/Closed]

Ask a question mad - Last answered on Feb 10, 2017 at 03:47 AM by Mark
Hello,
I would need your help with my problem in MS Excel.
Description:
1. I have an excel workbook containing multiple sheets (ca. 15-20)
2. Each of the sheets contains several records of the products identified by unique pr. number/code
3. So, I need to find a function or macro which will do following: if I enter selected pr. code in new sheet it will find/lookup it across the whole workbook (all the sheets) and will give the reference for the respective cell.

Any help with this will be greatly appreciated.
Thanks a lot in advance.

Martin
See more 
Helpful
+0
plus moins
This is from my notes. I have not personally checked recently. perhaps it will work.

In sheet1 (or any sheet) enter the sheet names. and NAME this range of cells as "Mysheets"
then use this formula

=VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&MySheets&"'!A1:A50"),A1)>0,0))&"'!A:B"),2,0)

invoke this formula with CONTROL+SHIFT+ENTER
stewartsetter leafxl- Apr 12, 2016 at 05:25 AM
with a little modification ( only minor to suit my data ) this works absolutely perfectly. This is looking across 30 different worksheets covering a range of 65,000 lines !!

If anyone is struggling to use this formula, you need to understand how both Index(Match()) and VlookUp() works first. One you have that understanding, this formula is child's play..... I added an Iferror statement because I loath getting N/A# 's....... I love it !

My script below :
{=IFERROR(VLOOKUP($H7,INDIRECT("'"&INDEX(MySheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&MySheets&"'!$B$1:$H$1000"),$H7)>0,0))&"'!B:H"),7,0),"")}

TIP
$H7 is the code I am looking up
$B$1:$H$1000 and B:H is the total range on each worksheet i'm looking up
7 is the column where the result I'm looking for
whaleh8er- Jul 26, 2016 at 01:52 PM
only one additional detail for any body else struggling like I was. open up the "Mysheets" sheet and highlight the list of sheet names. then rename the highlighted area "Mysheets". the bar up and to the left of the A1 cell, it usually says SUM in it.
once i renamed that range of cells in the sheet it this formula worked for me
Mark- Feb 10, 2017 at 03:47 AM
This is the formula i am using bu can't get it to work

=VLOOKUP(A1, INDIRECT("'"&INDEX(Mysheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&Mysheets&"'!$b$5:$b$1000"),A1)>0,0))&"'!b"),2,0)

Regrads,

Mark
Reply
Helpful
+0
plus moins
this is response to Jan posting, if possible upload your workbook with only three sheets and small amount of data in each sheet to
speedyshare.com
delete the password
post the webpage address.
Helpful
-1
plus moins
Hi Venkat,

I am not able to do this with the suggested formula, would you be able to help me please

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!