lookup match with multiple criteria

[Bobsone]

Hi

I have been going around in circles with this one... :-(

I want to take specific Sheet2 table totals and place them in the cell next to the matching Sheet1 code.
I need a formula to search and match the value in A4 (i.e. match sheet1!A4 with the Sheet2 A column (e.g. Sheet2!A3)), then search the
appropriate tables header row, matching the Sheet1 values B6:B28 and returning each values Total to the corresponding Sheet1 cell, C6:C28.

All of the tables on Sheet2 will always have the same layout but the number of rows per table will change. E.g. The tables currently have the rows AAA and BBB but could have up to 12 rows between "Pa 1" and the blank cell above the "Total" row.
Also, the number of Sheet2 tables will vary, ranging from 1 up to 27 separate tables each containing up to 12 rows between the table identifier (e.g. Pa 1) and the blank cell above each tables "Total" row. Each sheet2 table will have a unique identifier value in A3, A12, A21 etc.

Regards.

[AlanY]

not pretty, but work.

[Bernie Deitrick]

This should work for you.

lookup match.xlsx

[rcm]

rename sheet1 to output and clear all the data

Please Login or Register  to view this content.

[Bobsone]

HI
Thanks for the answers AlanY, Bernie Deitrick and rcm.

I have adapted and am currently using all 3 solutions... a bit of testing

Thanks again

Regards, Bob.

[rcm]

you are welcome, if one of them works for you just click on the solved button,,