Forum Discussion
Lookup / Indirect / match?
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.
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! 🙂
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.