Formula to find adjacent cell value in a given column from multiple sheets

I have a workbook in which I am importing lots of CSV sheets (over 100). All of the CSV that I have examined have identical formatting for columns, but not for rows. For example, Sheet40 and Sheet80 will both have a descriptive text in column AC, such as "Units," and the numeric value in the same row in column AD, but in Sheet40 it may be on row 16 and on Sheet80 it may be row 19. So I'll always know the column number and the name of the descriptive text, but not the row number.

On my calculations sheet, I have a column where I have the name of each sheet in column A and want to capture the units for each sheet in column B, how is the best way to do so?

-HeyInKy

I'm guessing this is a simple VLOOKUP formula, but that's really the one common formula I've not used much, if at all...