Look up and return data within a table between sheets

[KcoonsThur]

Hi,

I am creating an access database from excel data and am working on formatting the data. I have multiple workbooks with multiple sheets formatted identically so something i can copy into my other workbooks is essential. I know there is a fast way to pull the data I need but I am having a hard time with the logic. The attached workbook represents 2 sheets from 1 workbook. The full file has 4 sheets (not including the sheet i'm pulling the data to) to pull data from and about 100 pile IDs, and then I have 6 workbooks with the same format.

I have attached the workbook. I am wanting to pull the installation date and corresponding depth and time from a specific pile ID in Sheet: 2TA170 to the same pile ID in Sheet: Access Database. I am wanting to do the same with hammer stroke and its corresponding depth in the same manner. There are many blanks between the dates/times/depths/hammer strokes and multiple entries per each Pile ID. I'd like to fill all cells from column H to U for each Pile ID in Sheet: Access Database, from its corresponding table in Sheet: 2TA170.

Let me know if you require more clarification.

Regards,

Kyle

[Bo_Ry]

Please try at G3:G32

Please Login or Register  to view this content.

[KcoonsThur]

Hi Bo_Ry,

Thanks for taking the time to look at this.

Your solution works well but is returning the hammer stroke column (D for example) from sheet 2TA where I would like it to return column (B) from Sheet 2TA into Column I in Sheet Access Database. However, I'd like those hammer stroke columns from 2TA to appear in the associated columns in Access Database.

Essentially I'd like to know what the depth is at a specific date and time. All of the initial depths and times in row 8 sheet 2TA should return a depth of 0.0 m.

The result for row 3 in sheet access database should return as follows starting with column G to column L: 2/18/2020 12:56:00 PM 0.00 2/18/2020 2:41:00 PM 40.73

If its easier to make a single formula for each of the columns I don't mind that, I'd actually prefer it.

Let me know if i need to clarify further.

[JeteMc]

If I understand correctly Then the following formula will fill columns G:I

Please Login or Register  to view this content.

Columns G:H will need to be formatted as Date and Time respectively.
When the formula is applied to columns J:L references to row 8 will need to be changed to 9 i.e. =INDEX('2TA170_01_003-039'!$A$9:$FX$9,AGGREGATE(15,6,COLUMN($A$9:$FX$9)…
If I have misunderstood the request then it may be helpful if you could manually fill in the first few values that need to go into columns G:I on the Access Database sheet.
Let us know if you have any questions.