Jul 09 2021 01:40 AM - edited Jul 09 2021 02:45 AM
How do I obtain the headers (in the row 1) that are responsible for the result obtained in the cell AV3.
Say for all the values that sum up to 780 (cell AV3), sum not exceeding 1000, I want the corresponding name of the headers in the row 1 (TEXT 20, TEXT 21, TEXT 23, TEXT 26, TEXT 28, TEXT 29, TEXT 32) in one cell or may be in the adjacent cells. Test file attached
Kindly help.
Jul 09 2021 03:19 AM
Assuming that you have Excel 2019 or Excel in Microsoft 365:
In AX2, confirmed with Ctrl+Shift+Enter:
=TEXTJOIN(",",TRUE,IF(OFFSET(B2,,,,MATCH(AU2, SUBTOTAL(9,OFFSET(B2,,,,COLUMN($C$1:$AS$1)-2))))>0,OFFSET($B$1,,,,MATCH(AU2, SUBTOTAL(9,OFFSET(B2,,,,COLUMN($C$1:$AS$1)-2)))),""))
Fill down.
Jul 09 2021 03:23 AM
Jul 09 2021 03:26 AM
Jul 09 2021 03:49 AM
SolutionThe attached version contains a custom VBA function.
The workbook is now a .xlsm workbook, so you will have to allow macros when you open it.
Jul 10 2021 12:07 AM
Jul 09 2021 03:49 AM
SolutionThe attached version contains a custom VBA function.
The workbook is now a .xlsm workbook, so you will have to allow macros when you open it.