Lookup / Indirect / match?

[Cashworth]

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) of these reference numbers (Vlookup value), without Ctrl + F (workbook) paste reference number, select row, copy and paste for each reference number! Each of the tabs contain similar information but do not carry an exact format copy  i.e each tab has the same column headers, but in one tab it would be on "O" column,but in another in column "M". 

I was thinking along the lines of a Vlookup + Indirect + Match for the column headers of required info but i cannot seem to wrap my brain around it! If there is anyone who has a firmer grasp than i, the help would be greatly appreciated! 

Thanks,

[#2]

@Cashworth 

you can use match to find on which column your header is. it will return a number. and also use the same match formula to return row number of the cell which has the value you need. and combine these with offset or index formula. ofset formula requires a reference cell that you will move, number of rows and number of columns. so in ofsset you need to decrease the result value of the match formula by 1. and use 1 in width and height to return at least one cell

in index you need to select the range of all the table and then add match results to row and column areas. This will return the intersecting cell.

[#3]

@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! :) 

[#4]

@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.