+ Reply to Thread
Results 1 to 11 of 11

IFERROR lookup pulling from multiple number sequences in the same column

Hybrid View

  1. #1
    Registered User
    Join Date
    11-07-2011
    Location
    Arcata, California
    MS-Off Ver
    2011 for MAC version 14.1.0
    Posts
    52

    IFERROR lookup pulling from multiple number sequences in the same column

    I have a worksheet named NFT System Quote. We add quantities of products to this worksheet and an INDEX is built in a column next to these products.

    Formula in NFT System Quote

    =IF(OR(B25="",B25=0),"-",MAX(X$24:X24)+1)

    The items that are on the quote will get sequenced numbers starting with 1. The Herb Crop Invoice worksheet will reference the sequenced numbers and bring over the required data.

    Formula in Herb Crop Invoice

    =IFERROR(INDEX('NFT System Quote'!B:B,MATCH(ROWS($1:1),'NFT System Quote'!$X:$X,0)),"")

    Issue I am having is I have multiple numbered indexes that are all on the same column. The Lookup_array 'NFT System Quote'!$X:$X references the whole column and will only pull information for the first index.

    I did change the lookup_array to 'NFT System Quote'!X25:X54 but it did not work. Any suggestions?
    Attached Files Attached Files
    Last edited by ericinarcata; 04-28-2018 at 09:40 PM. Reason: Need one more formula

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,824

    Re: IFERROR lookup pulling from multiple INDEX

    As always, it would help if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

    I don't understand what you mean by this - "multiple numbered indexes that are all on the same column", so perhaps your example file will help to clarify what this is.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    11-07-2011
    Location
    Arcata, California
    MS-Off Ver
    2011 for MAC version 14.1.0
    Posts
    52

    Re: IFERROR lookup pulling from multiple INDEX

    Thanks. Example has been uploaded.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,824

    Re: IFERROR lookup pulling from multiple INDEX

    Why are you starting counting at row 25, rather than row 19?

    It seems that you want to reset the counter whenever there is text in column B - is that right?

    Pete

  5. #5
    Registered User
    Join Date
    11-07-2011
    Location
    Arcata, California
    MS-Off Ver
    2011 for MAC version 14.1.0
    Posts
    52

    Re: IFERROR lookup pulling from multiple INDEX

    Quote Originally Posted by Pete_UK View Post
    Why are you starting counting at row 25, rather than row 19?

    It seems that you want to reset the counter whenever there is text in column B - is that right?

    Pete
    Hey PETE. The cells above B25, generate the values in the other cells. I don't want them to show up on the invoice.
    Yes. I want to reset the counter for each section on the NFT System Quote. Growing System - Herb and Lettuce AT FRAME, Propagation - Herb and Lettuce, System Lighting, Tank House, etc...

    Thanks,

    Eric

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,370

    Re: IFERROR lookup pulling from multiple INDEX

    I would make each "sub-group" a named range (highlighted in example)and use the following formula:

    =IFERROR(INDEX(Growing_System,MATCH(ROWS($1:1),INDEX(Growing_System,,24),0),4),"")

    Sample for "Growing System" and "System Lighting" in attached.
    Attached Files Attached Files
    Last edited by JohnTopley; 04-27-2018 at 01:47 PM.

+ 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. Index, match large with multiple criteria pulling wrong record
    By markclary in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-29-2017, 08:00 AM
  2. [SOLVED] Using multiple context in one formula (Iferror/Index/Small/IF/Row)
    By gilpin004 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-14-2017, 01:32 PM
  3. Replies: 4
    Last Post: 08-17-2016, 04:06 PM
  4. IFERROR, INDEX, MATCH AND SUMIFS to get from multiple pages
    By kgw in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-31-2015, 02:59 PM
  5. IFERROR INDEX/MATCH for multiple sheets
    By mehare in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-22-2015, 12:47 PM
  6. Replies: 5
    Last Post: 04-28-2014, 05:41 PM
  7. [SOLVED] Pulling multiple index results from a single input number
    By cwatsonSONA in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-11-2013, 11:00 AM

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