Forum Discussion

Copper Contributor

Sep 11, 2019

Lookup / Indirect / match?

Background There is a database of 1000's of rows, each with a unique reference number. These are distributed in different tabs (40+ tabs!) Task Export the information relating to just 400 (ish)...

excel

Formulas and Functions

office 365

training

Cashworth

Copper Contributor

Sep 11, 2019

erol sinan zorlu

Thanks for the response! 🙂 One problem with the task i have a list of references to look up across many worksheet tabs (i think 50+ different worksheets in one workbook). Would this method have to be repeated with all references, for all tabs? i would consolidate the data to make one look up area but not all the information is stored in consistent columns 😞

Thanks again for the response, set off a new trail-of-thought! 🙂

erol sinan zorlu

Iron Contributor

Sep 11, 2019

Cashworth hello,

there are several ways to merge data. the easiest way seems to be the power query however my experience with it is limited. With this functionality you can create tables (insert table) for all the information on all the tabs and then join-transform these tables. and use the final data in index-match formulas. I am sure there is also a query to find any data you wish however it uses a language called M language which I do not have any experience.