Previous Topic Next Topic

Lookup across whole workbook (muliple sheets)

Solved/Closed
mad - 30 Jan 2010 à 13:04
 Mark - 10 Feb 2017 à 03:47
Hello,

I would need your help with my problem in MS Excel. Here is the 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!
System Configuration: Windows XP Safari 532.5
Related:

3 responses

Réponse 1 / 3
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
30 Jan 2010 à 20:27
Hey,

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.

Hope, it will resolve the issue!
stewartsetter leafxl
12 Apr 2016 à 05:25
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 > stewartsetter leafxl
26 Jul 2016 à 13:52
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
10 Feb 2017 à 03:47
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

Similar discussions

Find word within cell & put value in adjacent
Mr.G -
BogelBot -

39 responses
Code to move rows into another sheet based on certain values
nathaliecannell -
vcoolio -

1 response
If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses
Copy data onto sheet only if cell has text
danipants -
checjb -

17 responses
Match partial text string in one sheet to another text string in another sheet
Santos -
Santos -

2 responses
Compare values in two columns and return the value from third
ramdubai -
keshav -

11 responses