0
Thanks

A few words of thanks would be greatly appreciated.

Lookup across whole workbook (multiple sheets)




Issue


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.

Solution


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

Note


Thanks to venkat1926 for this tip on the forum.
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Related

This document, titled « Lookup across whole workbook (multiple sheets) », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

0 Comments