Search for Values in Multiple Data Tables Simultaneously

Per the attachment:

Looking to either write a function or VBA code to search for a certain value in a dataset that doesn't have consistent ordering of tables over the course of many periods. For example, I'm looking to find the "Amount" number for Company 5 for each year and list them across a single row. This is just a sample of the dataset I'm using that has over 100 periods and 1,000 companies.

I know I can run a vlookup for each year, but looking to save time since I would have to re-write the formulas for each year and there are over 100 periods.

If you have any ideas please let me know, thanks.

I create a result in next WS with unique company name go down and year go accross
See attachment.

This is really helpful, thank you so much. One issue I'm running in to is that the output I'm getting is doubling the column number to the right. For instance, my data begins in Column AD (2016 data, that's 30 columns over) and using the exact same formula outputs data in column BH (2010 data, that's 60 columns over)?

EDIT: I changed the +1 in your formula for column identifier in the INDEX function to +28 and it worked like a charm. Thanks again, you're a life saver!

Nice to hear it works!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.