+ Reply to Thread
Results 1 to 6 of 6

Trouble with formula Indirect Match

  1. #1
    Registered User
    Join Date
    10-29-2013
    Location
    Brisbane, Australia
    MS-Off Ver
    office 365
    Posts
    42

    Trouble with formula Indirect Match

    Hi guys, having trouble with the following formula.

    =IFERROR(OFFSET(INDIRECT("A"&2),INDEX( MATCH(1,(--(INDIRECT("A"&2):INDIRECT("A"&10)<>"")),0),1)-1,0),"")

    Obviously with this formula i can find the first cell between cells A2 and A10 that is not blank and say enter it in cell C1. Though what I need to do is modify this slightly so that it will pick up then 2nd and 3rd cells in the range between A2 and A10 that is not blank also and post these in cells D1,E1,F1...
    I have attached a demo for what I need. So cell C1 is correct answer but cell D1 should be PRODUCT F and cell E1 should be PRODUCT H.

    Any help greatly apprectiated.

    Thanks,

    Kelly.
    Attached Files Attached Files

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Trouble with formula Indirect Match

    In C1 Cell - Array Formula - Requires CTRL+SHIFT+ENTER


    =IFERROR(INDEX($A$2:$A$12,SMALL(IF($A$2:$A$12<>"",ROW($A$2:$A$12)-MIN(ROW($A$2:$A$12))+1),COLUMNS($A:A))),"")


    Drag it to right...

    Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Trouble with formula Indirect Match

    Hi, Apply the below formula in c1 and drag it across

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Click just below left if it helps, Boo?ath?

  4. #4
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Trouble with formula Indirect Match

    Hi, ref the attachment...
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-29-2013
    Location
    Brisbane, Australia
    MS-Off Ver
    office 365
    Posts
    42

    Re: Trouble with formula Indirect Match

    Thanks guys for all the help. Very much appreciated.

  6. #6
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Trouble with formula Indirect Match

    Hi, Thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Trouble with INDIRECT formula
    By stblu in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-16-2013, 06:12 AM
  2. Trouble with index/match formula
    By mstar93 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-05-2013, 08:27 PM
  3. [SOLVED] Trouble Converting Match Formula to VBA
    By PY_ in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-10-2013, 09:11 PM
  4. [SOLVED] Trouble with Index and match formula
    By mdot218 in forum Excel General
    Replies: 2
    Last Post: 04-03-2012, 10:41 AM
  5. Trouble joining a formula to be direct and indirect
    By curtwphillips in forum Excel General
    Replies: 1
    Last Post: 04-27-2011, 08:27 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1