+ Reply to Thread
Results 1 to 7 of 7

INDEX/MATCH with multiple arrays. Possibly need IF Function as well?

  1. #1
    Registered User
    Join Date
    01-31-2015
    Location
    North Montpelier, Vermont, USA
    MS-Off Ver
    2010
    Posts
    4

    INDEX/MATCH with multiple arrays. Possibly need IF Function as well?

    I have a spreadsheet that lists a unique number (ID) and other unique numbers (Part) that are associated with the ID. Some of those Parts have an associated (Price) that I want to have returned to me. But I only want the formula to focus on a select few parts (Possible Parts) to get the Price. I'm admittedly green in Excel so I'm trying my best to explain what I need. In below example if I entered an ID of 124 into cell A2, I want the formula to search column D for 124. I then want it to find the Part in column E that also matches the available Possible Parts in column H. So if 124 was entered into A2, then B2 should result with $130,000.

    If there is a better way to present this post, please let me know. Thanks.

    Added attachment.....
    Attached Files Attached Files
    Last edited by guy lafleur; 01-31-2015 at 02:04 AM.

  2. #2
    Registered User
    Join Date
    01-31-2015
    Location
    North Montpelier, Vermont, USA
    MS-Off Ver
    2010
    Posts
    4

    Re: INDEX/MATCH with multiple arrays. Possibly need IF Function as well?

    Well that didn't come out at all how I wanted it to. How can I add show cells in this space?

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: INDEX/MATCH with multiple arrays. Possibly need IF Function as well?

    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    01-31-2015
    Location
    North Montpelier, Vermont, USA
    MS-Off Ver
    2010
    Posts
    4

    Re: INDEX/MATCH with multiple arrays. Possibly need IF Function as well?

    I think I did it....
    Attached Files Attached Files

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: INDEX/MATCH with multiple arrays. Possibly need IF Function as well?

    Yup, you did

    See if this is what you need...
    =INDEX($G$3:$G$11,IFERROR(MATCH($B$3&$I$3,INDEX($E$3:$E$11&$F$3:$F$11,0),0),MATCH($B$3&$I$4,INDEX($E$3:$E$11&$F$3:$F$11,0),0)))

  6. #6
    Registered User
    Join Date
    01-31-2015
    Location
    North Montpelier, Vermont, USA
    MS-Off Ver
    2010
    Posts
    4

    Re: INDEX/MATCH with multiple arrays. Possibly need IF Function as well?

    Wow! That definitely is what I'm after. I'm gonna take a few minutes to digest what's happening in the formula, but at first glance I see what you're doing. I didn't realize you could have the MATCH (B3&I3) written that way. I"ll let you know if I need anything else!

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: INDEX/MATCH with multiple arrays. Possibly need IF Function as well?

    Happy to help

+ 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 Function with Multiple Arrays
    By btone in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-22-2014, 03:59 PM
  2. Problem with reference to multiple arrays in INDEX/MATCH combo
    By tnuis in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-24-2013, 08:10 PM
  3. Problems with reference to multiple arrays using VLOOKUP / INDEX-MATCH
    By tnuis in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-24-2013, 07:21 AM
  4. [SOLVED] Advanced Match Help (possibly Index Match)
    By dfxryanjr in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-08-2013, 09:13 AM
  5. Replies: 7
    Last Post: 03-26-2009, 12:13 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