Hi
I am trying to lookup a value in Column A then look across that row to find a string and return the Column header. There may be multiple occurrences of the string in that row and I need to return all column headers where there is a match. The data I am looking up is on a closed workbook.
I am able to get the column header of the first match using the following formula:
=INDEX('C:\Users\***\[workbookname.xlsm]Log'!$M$5:$FP$5,MATCH("NO",INDEX('C:\Users\***\[workbookname.xlsm]Log'!$M$6:$FP$2000,MATCH(A2,'C:\Users\***\[workbookname.xlsm]Log'!$A$6:$A$2000,0),),0))
But I am unsure how to fill that formula right to get any further matches in that row.
E.g.
Closed lookup workbook:
Column A .. Column M Column N Column O Column P Column Q .. Column FP
1 NAME 1a 2a 3a 4a 5a 160a
.
.
5 ABC NO NO
6 DEF NO NO
7 GHI NO NO NO
8 JKL NO NO
9 MNO NO NO NO
10 PQR NO NO NO
Workbook with formula:
Column A Column B Column C Column D Column E Column F
NAME Occurrence 1 Occurrence 2 Occurrence 3 Occurrence 4 Occurrence 5
1 MNO 2a
2 ABC 1a
3 PQR 1a
I can get the formula to populate Column B to get the first column header but don't know how to drag the formula across Columns C:F to return the next 4 occurrences (there may be <> 5 occurrences for each NAME). The table should then look like this:
Column A Column B Column C Column D Column E Column F
NAME Occurrence 1 Occurrence 2 Occurrence 3 Occurrence 4 Occurrence 5
1 MNO 2a 4a 5a
2 ABC 1a 4a
3 PQR 1a 3a 155a
Any help will be greatly appreciated!
I am trying to lookup a value in Column A then look across that row to find a string and return the Column header. There may be multiple occurrences of the string in that row and I need to return all column headers where there is a match. The data I am looking up is on a closed workbook.
I am able to get the column header of the first match using the following formula:
=INDEX('C:\Users\***\[workbookname.xlsm]Log'!$M$5:$FP$5,MATCH("NO",INDEX('C:\Users\***\[workbookname.xlsm]Log'!$M$6:$FP$2000,MATCH(A2,'C:\Users\***\[workbookname.xlsm]Log'!$A$6:$A$2000,0),),0))
But I am unsure how to fill that formula right to get any further matches in that row.
E.g.
Closed lookup workbook:
Column A .. Column M Column N Column O Column P Column Q .. Column FP
1 NAME 1a 2a 3a 4a 5a 160a
.
.
5 ABC NO NO
6 DEF NO NO
7 GHI NO NO NO
8 JKL NO NO
9 MNO NO NO NO
10 PQR NO NO NO
Workbook with formula:
Column A Column B Column C Column D Column E Column F
NAME Occurrence 1 Occurrence 2 Occurrence 3 Occurrence 4 Occurrence 5
1 MNO 2a
2 ABC 1a
3 PQR 1a
I can get the formula to populate Column B to get the first column header but don't know how to drag the formula across Columns C:F to return the next 4 occurrences (there may be <> 5 occurrences for each NAME). The table should then look like this:
Column A Column B Column C Column D Column E Column F
NAME Occurrence 1 Occurrence 2 Occurrence 3 Occurrence 4 Occurrence 5
1 MNO 2a 4a 5a
2 ABC 1a 4a
3 PQR 1a 3a 155a
Any help will be greatly appreciated!
)
