Dynamic Index/Match function when column location is unknown?

I am trying to collect data from multiple worksheets into one worksheet, generally I can accomplish this using INDEX/MATCH/INDIRECT to create one dynamic formula that can accomplish it. The problem I have now is that on each worksheet all the columns are in a different order. The main problem is that my unique identifier is for each row is in a different position on each sheet. I know a basic solution would be to copy the unique identifier and place it in the first column for each sheet but I need to create a dynamic formula that makes this possible without moving any columns around.


If I need to expand on what I am trying to do let me know and I can.

I will take a look at that video but let me try to expound:

I have 5 bond portfolios, each bond is being identified by its unique CUSIP, each portfolio is on its own tab and each tab has all the same column headers but in a different order. I need to create an aggregate "Master Portfolio" tab where I can see all the data laid out uniformly. If the CUSIP was in Column A I would be able to create a single dynamic formula using INDEX (for lookup), MATCH (find column headers) and INDIRECT(to get to the proper tab).

However, CUSIP is in a different column each time so I cant define a static column to find the CUSIP in my match formula. I need a way to somehow tell it to match to my column "CUSIP" regardless of what column it is.

Make more sense?

Thanks for the video but that is no applicable. I generally use the standard index match function as described in the video but it wont work in my example. I am going to try again to explain...

I have five tabs and i need to pull the data into one aggregate tab. Each tab has all the same columns, but in random order. When I use the index/match I need to tell it lookup and match "value x" in this specific column and then lookup and match it against these column headers. In my example I cannot state which column "value x" is in and I need a formula that will figure it out on its own.

Does that make more sense? If not, I will dumb down the workbook and post it.