+ Reply to Thread
Results 1 to 6 of 6

Index/Match array help please

  1. 11-17-2015, 09:46 PM #1
    EimearC
    EimearC is offline
    Registered User
    Join Date
    07-10-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    31

    Index/Match array help please

    Hi,

    I am having trouble with the below formula

    =IF(AND(Tracker!$W$4:$W$31<>"",Tracker!$AA$4:$AA$31=""),INDEX(Tracker!B3:AA31,2,COLUMNS($B$8:B8)),"")

    I have a list of sites in Tracker sheet column B, I have dates in Tracker sheet columns W & AA.
    On my current sheet I would like a list of sites (Tracker sheet column B) to display if there is a date/text/not blank in Tracker sheet Column W & nothing in the Tracker Sheet Column AA.

    Currently the above works if there is nothing in ALL the columns of column AA, I'm missing a match row function or something similar I think but any assistance would be appreciated.
    Reply With QuoteRegister To Reply
  2. 11-17-2015, 10:05 PM #2
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Index/Match array help please

    To best describe your question it is best to attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data Click on GO ADVANCED and use the paperclip icon to open the upload window.
    Dave
    Reply With QuoteRegister To Reply
  3. 11-17-2015, 10:07 PM #3
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,459

    Re: Index/Match array help please

    Try to fix:
    Tracker!B3:AA31 by Tracker!$B$3:$AA$31
    or
    AND(Tracker!$W4<>"",Tracker!$AA4="")
    if it does not work, try to upload a small worksheet.
    Quang PT
    Reply With QuoteRegister To Reply
  4. 11-17-2015, 10:36 PM #4
    EimearC
    EimearC is offline
    Registered User
    Join Date
    07-10-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    31

    Re: Index/Match array help please

    Milestone TrackerEC.xlsx

    Hi,
    I have attached a sample workbook now to hopefully help.

    The formula is in 'Status Board' sheet B8 & referencing to 'Tracker' sheet columns W (must all have text) & columns AA (Display if empty)

    Thanks
    Reply With QuoteRegister To Reply
  5. 11-17-2015, 11:06 PM #5
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,459

    Re: Index/Match array help please

    Is it the case of extract list of sites with corresponding columns? 

    Please Login or Register  to view this content.
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    Drag down and accross.
    Reply With QuoteRegister To Reply
  6. 11-17-2015, 11:52 PM #6
    EimearC
    EimearC is offline
    Registered User
    Join Date
    07-10-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    31

    Re: Index/Match array help please

    Beautiful
    Thank you so much!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays
    By Numnum in forum Excel General
    Replies: 2
    Last Post: 10-15-2015, 02:08 PM
  2. [SOLVED] index/match array help
    By jwoodarek in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-08-2015, 03:34 PM
  3. Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY fail
    By sfike in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  4. [SOLVED] Index Match Array
    By namluke in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-02-2014, 09:55 AM
  5. Index Match array equation with sub-array calculation
    By glebbo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-04-2012, 03:04 AM
  6. Nested Match(index()) vs. Match() array formula.
    By GeneralDisarray in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-14-2011, 07:57 AM
  7. [SOLVED] Using Match/Index on 2d Array
    By ExcelMonkey in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-21-2006, 09:30 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1