excelexcel-formulaheadermatchlookup-tables

MS Excel - Search Across Multiple Headers & Return Multiple Matching Values based on Corresponding Headers

Using a MS Excel formula, I would like to Search across multiple Table Headers located in Worksheet 1 (ROWS B1:I1) and Return Multiple matching Values in Worksheet 1 (ROWS B2:I9) in Worksheet 2 based on both Matching Table Headers located in Worksheet 1 and Worksheet 2.

I've attempted Index Match and Filter formulas without success... help is greatly appreciated.

enter image description here

Solution

  • You can use either INDEX( ) with MATCH( ) function or XLOOKUP( )

    enter image description here

    • Formula used in cell A2

    =IFERROR(INDEX('WORKSHEET 1'!$A$2:$H$9,,
MATCH('WORKSHEET 2'!A$1,'WORKSHEET 1'!$A$1:$H$1,0)),"")

    Or, enter image description here

    • Formula can also be used in cell A2

    =XLOOKUP(A$1,'WORKSHEET 1'!$A$1:$H$1,'WORKSHEET 1'!$A$2:$H$9)

    Ofcourse both formulas need to be filled across!

    Or, Using FILTER( ) Function.

    enter image description here

    • Formula used in cell A2

    =FILTER('WORKSHEET 1'!$A$2:$H$9,'WORKSHEET 1'!$A$1:$H$1='WORKSHEET 2'!A$1)

    With one single dynamic array formula which spills.

    enter image description here

    • Formula used in cell A2

    =LET(
     a,A1:E1,
     b,'WORKSHEET 1'!A1:H1,
     c,'WORKSHEET 1'!A2:H9,
     INDEX(c,SEQUENCE(ROWS(c)),XMATCH(a,b)))

    Workbook link can be downloaded from here