What to use - If Statement? Index & Match lookup? Or Vlookup?

[HelpMeWithExcelPlease]

Hi all,

I am working with the following data and am trying to identify the appropriate formula to use to speed up this process. I do not have a ton of familiarity with INDEX(MATCH()) formulas, and I think what I am looking for cannot be satisfied by a VLOOKUP() alone. Any ideas on what I can do would be greatly appreciated!

I have data from a few different documents that I am trying to consolidate into one worksheet. Those input tables are shown in the below two tables:

Input Table #1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Defect ID[/TD]
[TD]Field Name[/TD]
[TD]Interface #1[/TD]
[TD]Interface #2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]first_name[/TD]
[TD][/TD]
[TD="align: center"]X[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]last_name[/TD]
[TD="align: center"]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]zip_code[/TD]
[TD][/TD]
[TD="align: center"]X
[/TD]
[/TR]
</tbody>[/TABLE]

Input Table #2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Defect ID[/TD]
[TD]Field Name[/TD]
[TD]Interface #3[/TD]
[TD]Interface #4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]zip_code[/TD]
[TD="align: center"]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]phone_number[/TD]
[TD="align: center"]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]first_name[/TD]
[TD][/TD]
[TD="align: center"]X[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]state_ID[/TD]
[TD][/TD]
[TD="align: center"]X

[/TD]
[/TR]
</tbody>[/TABLE]

The worksheet I am trying to consolidate the data on to looks like the below sample table. What I am looking for is a formula (or formulas) to put in cells C2:F8 that will pull in the "X" from the Input tables if applicable.
Consolidated Table
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Defect ID[/TD]
[TD]Field Name[/TD]
[TD]Interface #1[/TD]
[TD]Interface #2[/TD]
[TD]Interface #3[/TD]
[TD]Interface #4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]first_name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]last_name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]zip_code[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]phone_number[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]first_name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]work_address[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]state_ID[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any assistance, suggestions, or resources that you all can provide would be awesome, thank you in advance!

 

[njimack]

Try the below (confirm with ctrl+shift+enter) - note, you'll need to change the reference to C$12 - this was the cell of my consolidated table that contained "Interface #1 "...

=IF(COUNTIF(Table1[Defect ID],[@[Defect ID]])*COUNTIF(Table1[Field Name],[@[Field Name]])*COUNTIF(Table1[#Headers],C$12),INDEX(Table1,MATCH([@[Defect ID]]&[@[Field Name]],Table1[Defect ID]&Table1[Field Name],0),MATCH(C$12,Table1[#Headers],0)),IF(COUNTIF(Table2[Defect ID],[@[Defect ID]])*COUNTIF(Table2[Field Name],[@[Field Name]])*COUNTIF(Table2[#Headers],C$12),INDEX(Table2,MATCH([@[Defect ID]]&[@[Field Name]],Table2[Defect ID]&Table2[Field Name],0),MATCH(C$12,Table2[#Headers],0)),""))