How to check if a cell matches another, and if it matches, to match another, and return the column.

%3CLINGO-SUB%20id%3D%22lingo-sub-1947119%22%20slang%3D%22en-US%22%3EHow%20to%20check%20if%20a%20cell%20matches%20another%2C%20and%20if%20it%20matches%2C%20to%20match%20another%2C%20and%20return%20the%20column.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1947119%22%20slang%3D%22en-US%22%3E%3CP%3ERather%20lengthy%20title%2C%20but%20i'll%20break%20it%20down.%3C%2FP%3E%3CP%3EWorkbook%20A%20contains%202%20sheets.%20Sheet%201%20contains%202%20columns%20in%20C%20and%20D.%20Column%20C%20has%20names%20of%20headers%20while%20column%20D%20has%20the%20abbreviation%20form%20of%20the%20header.%20In%20sheet%202%2C%20it%20contains%20all%20the%20data%20formatted%20in%20columns%20with%20headers%20in%20a%20non%20abbreviated%20format%20as%20represented%20in%20column%20C.%3C%2FP%3E%3CP%3EIn%20workbook%20B%2C%20it%20contains%201%20sheet%20with%20headers%20but%20no%20data.%20All%20headers%20in%20workbook%20B%20are%20using%20the%20Column%20D%20abbreviation%20format.%3C%2FP%3E%3CP%3EExample%20of%20what%20I%20mean%3A%20C1%20%3D%20Unique%20Identifier%20%7C%20D1%20%3D%20UID%3C%2FP%3E%3CP%3EWhat%20I%20intend%20to%20achieve%20is%20to%20have%20a%20formula%20that%3A%3C%2FP%3E%3CP%3EIn%20an%20empty%20cell%20below%20the%20header%20in%20workbook%20B%2C%20to%20search%20for%20the%20header%20if%20it%20is%20found%20in%20any%20of%20the%20cells%20under%20Column%20D%20of%20workbook%20A%2C%20sheet%201.%20If%20a%20match%20is%20found%2C%20to%20then%20match%20the%20cell%20in%20Col%20C%20to%20that%20of%20the%20headers%20in%20Sheet%202%20of%20workbook%20A%2C%20and%20then%20extract%20out%20all%20the%20rows%20under%20that%20column%20into%20the%20empty%20cell%20mentioned.%3C%2FP%3E%3CP%3EAny%20help%20is%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20what%20I%20have%20so%20far%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX('%5BDQA%20DataSource.xlsx%5DData'!%24A%243%3A%24KB%242011%2CROW()-2%2CMATCH(INDEX(%5BDQADataSource.xlsx%5DDirectory!%24C4%3A%24C60%2CMATCH(A%242%2C%5BDQADataSource.xlsx%5DDirectory!%24D4%3A%24D60%2C0))%2C%5BDQADataSource.xlsx%5DData!%241%3A%241%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20current%20formula%20returns%20an%20N%2FA%20error%20that%20im%20unable%20to%20get%20fixed.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1947119%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1947238%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20check%20if%20a%20cell%20matches%20another%2C%20and%20if%20it%20matches%2C%20to%20match%20another%2C%20and%20return%20the%20colu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1947238%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F886973%22%20target%3D%22_blank%22%3E%40millennnn%3C%2FA%3E%26nbsp%3BTry%20by%20making%20both%20references%20to%20%22Directory%22%20columns%20C%20and%20D%20absolute%20for%20both%20the%20column%20AND%20row%20part%3A%3C%2FP%3E%3CP%3E%3CSPAN%3E%24C%3CFONT%20color%3D%22%23FF0000%22%3E%24%3C%2FFONT%3E4%3A%24C%3CFONT%20color%3D%22%23FF0000%22%3E%24%3C%2FFONT%3E60%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%24D%3CFONT%20color%3D%22%23FF0000%22%3E%24%3C%2FFONT%3E4%3A%24D%3C%2FSPAN%3E%3CSPAN%3E%3CFONT%20color%3D%22%23FF0000%22%3E%24%3C%2FFONT%3E60%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

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.

1 Reply

@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