HLOOKUP - reference ranges listed in other cells.
When using VLOOKUP or HLOOKUP in Excel, reference the array for the formula, if it happens to be listed in another cells, using the INDIRECT formula, but reference, the column index number, also listed in another cell, which a regular cell number.
You can use this basic approach to pull data with HLOOKUP from a worksheet that lists the content of multiple source Excel files, and includes headings. Headings which vary between source files can be standardized on a new worksheet, and the data for each collected with HLOOKUP.
Sorry folks. Litigation Support Tip of the Night is badly sleep deprived. These tips will improve when he is no longer working on a trial.