Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    398
    Thanks
    3
    Thanked 0 Times in 0 Posts

    INDEX MATCH Formula not working

    I am attaching a workbook with two tabs. One is CENTRAL OFFICE and the second is SUBSIDIARY.

    i am trying to bring over from SUBSIDIARY to CENTRAL OFFICE certain data using the INDEX MATCH functions, however it is not working.

    I would appreciate assistance in correcting the formula or informing me why the CENTRAL OFFICE worksheet cannot pull in the SUBSIDIARY data.

    Thanks you

    ps: I keep hearing that Index Match is much better than VLOOKUP. So far not so in my opinian.

    Thank you.
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,922
    Thanks
    152
    Thanked 747 Times in 679 Posts
    MNN,

    Attached is your sheet with the formulas corrected. In the match formula, the lookup array must be only one column not a range of columns.

    HTH,
    Maud

    MNN1.png
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    398
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Maud,

    your solution does not 'AUTOMATE" the COLUMN COUNT number of the INDEX formula. In your solution, the COLUMN COUNT Function is hard coded. My readings indicate this could be automated like the ROW COUNT Function.

    Am I incorrect on this?

    Thank you

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,922
    Thanks
    152
    Thanked 747 Times in 679 Posts
    MNN,

    Attached are the formulas you seek. They will automatically adjust the index column based on a horizontal match of the header.

    I Converted exclusively from Vlookup to Index/Match. While the comparable speed is debatable, I enjoy the ability to return values from any column while adding rows and columns that do not bust the formulas.

    HTH,
    Maud


    Code:
    Cell F5 =IFERROR(INDEX(SUBSIDIARY!$A$7:$L$13,MATCH($B5,SUBSIDIARY!$C$7:$C$13,0),MATCH(F$4,SUBSIDIARY!$A$6:$L$6,0)),"")
    Copy down then accross
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    398
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Maud,

    After playing around with your formula, I was able to automate the COLUMN COUNT function successfully. Now onto the real workbooks.

    Thank you so much.

    MNN

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,570
    Thanks
    44
    Thanked 73 Times in 69 Posts
    MNN, do you mean something like this (with a "helper" in H2 that could be placed in the formula directly).

    Code:
    =IFERROR(INDEX(INDIRECT("SUBSIDIARY!$A$7:$L$"&$H$2),MATCH($B5,INDIRECT("SUBSIDIARY!$C$7:$C$"&$H$2),0),1),"")
    This will handle 1000 as the last row in SUBSIDIARY.

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,922
    Thanks
    152
    Thanked 747 Times in 679 Posts
    MNN,

    If you want to go more generic with unlimited number of rows, in F5 place the following formula which uses columns as opposed to ranges in columns then copy down. Then copy across.

    Consider using the match formula for the header to find the index column. This way, you can change the order of the columns or add a column without adjusting the formulas.

    Code:
    =IFERROR(INDEX(SUBSIDIARY!$A:$L,MATCH($B5,SUBSIDIARY!$C:$C,0),MATCH(F$4,SUBSIDIARY!$A$6:$L$6,0)),"")

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •