Dec 01 2020 08:36 PM - edited Dec 01 2020 08:38 PM
Rather lengthy title, but i'll break it down.
Workbook A contains 2 sheets. Sheet 1 contains 2 columns in C and D. Column C has names of headers while column D has the abbreviation form of the header. In sheet 2, it contains all the data formatted in columns with headers in a non abbreviated format as represented in column C.
In workbook B, it contains 1 sheet with headers but no data. All headers in workbook B are using the Column D abbreviation format.
Example of what I mean: C1 = Unique Identifier | D1 = UID
What I intend to achieve is to have a formula that:
In an empty cell below the header in workbook B, to search for the header if it is found in any of the cells under Column D of workbook A, sheet 1. If a match is found, to then match the cell in Col C to that of the headers in Sheet 2 of workbook A, and then extract out all the rows under that column into the empty cell mentioned.
Any help is appreciated.
This is what I have so far:
=INDEX('[DQA DataSource.xlsx]Data'!$A$3:$KB$2011,ROW()-2,MATCH(INDEX([DQADataSource.xlsx]Directory!$C4:$C60,MATCH(A$2,[DQADataSource.xlsx]Directory!$D4:$D60,0)),[DQADataSource.xlsx]Data!$1:$1,0))
This current formula returns an N/A error that im unable to get fixed.
Dec 01 2020 09:37 PM
@millennnn Try by making both references to "Directory" columns C and D absolute for both the column AND row part:
$C$4:$C$60
$D$4:$D$60