+ Reply to Thread
Results 1 to 5 of 5

Using Index/Match (Equal or less than) with Multiple Criteria

  1. #1
    Registered User
    Join Date
    07-05-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    3

    Using Index/Match (Equal or less than) with Multiple Criteria

    Hello everyone,

    I've managed to solve most of this but I am now stuck on the final part and can't seem to find anyway to solve this.

    I've attached a sample spreadsheet showing exactly what I'm trying to do and what isn't working here:
    Padal_Excel_Lookup_Test.xlsx

    Basically I have list of items categorized under a Main Category and Sub Category. The user selects the Main Category from a drop-down list, types a number for the Sub Category and using Index/Match lookup I then display some info about their selected item.

    The array formula I'm using to do the lookup at the moment (which half works) is:

    Please Login or Register  to view this content.
    Now if the user selects a category and enters a sub-category number that exists the formula works fine.

    The problem is when a user types in a number for Sub-Category... if that Sub Category number doesn't exist then I get a N/A# error. Instead the lookup formula needs to return the row number of the next lowest number for that Main Category.

    For example if you have a look at the spreadsheet I attached... you'll see that if the user selects Main Category BEF and Sub Category number 5, this combination does exist and so the formula will return Row 8 (so the info from C8 will be displayed).

    On the other hand if the user selects Main Category BEF and Sub Category 8, this doesn't exist so the formula needs to find the next lowest number that does exist (in this case it's number 5) and return Row 8 like above.

    So I tried to fix this by changing the MATCH formula to -1 but now the formula always returns N/A# even tho I've entered it correctly as an array formula. So I'm now totally stuck with this and was hoping someone could please help me figure out where I'm going wrong or let me know how I can do this?

    Thanks in advance for any help you can give me.
    Last edited by Padal; 07-05-2012 at 12:17 PM. Reason: Realised I wrote a novel and re-worded it shorter

  2. #2
    Registered User
    Join Date
    07-05-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Using Index/Match (Equal or less than) with Multiple Criteria

    Hey guys,

    I think I may have found a possible solution but I'm not sure of the correct syntax for it. This is sort of a separate question so I'm not sure if this should be a new post.

    What I was wondering was how do you use INDEX with a custom array range?

    For example what I want to do is this:

    Please Login or Register  to view this content.
    So basically instead of specifying a lookup array of A1:A50 or a named range, I would like to specify a custom start/end point for the lookup array however I can't seem to find the correct syntax for this. Could someone please let me know how this should be worded?

    Thanks!

  3. #3
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Using Index/Match (Equal or less than) with Multiple Criteria

    Padal,

    Welcome to the forum!
    Attached is a modified version of your posted workbook.
    I used Dynamic Named Ranges to create ranges that will automatically expand to include new data.

    For the Main Category (column A) I named it MainCategory and it uses this formula:
    Please Login or Register  to view this content.

    For the Sub Category (column B) I named it SubCategory and it uses this formula:
    Please Login or Register  to view this content.

    For the Item Info (column C) I named it ItemInfo and it uses this formula:
    Please Login or Register  to view this content.

    Then, in cell F5 is this formula that should accomplish what you're looking for:
    Please Login or Register  to view this content.

    Here's some links for more information on dynamic named ranges:
    http://support.microsoft.com/kb/830287
    http://office.microsoft.com/en-us/ex...001126115.aspx
    http://www.cpearson.com/excel/DefinedNames.aspx
    http://blog.contextures.com/archives...automatically/
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    07-05-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Using Index/Match (Equal or less than) with Multiple Criteria

    Wow thank you that is awesome!

    I have a couple of very similar scenarios needing similar sort of formulas and I've been spending quite a bit of time figuring out what your formula does and then modifying it for my other needs. I can now happily report that thanks to your help everything is now working great... so thank you.

    Furthermore thank you very much for the info on dynamic named ranges as well. I normally do use ranges and always thought "dynamic ranges" is what I was doing. I've only just realised that I no longer need to define the exact size of a range and this info has already proven very useful on another spreadsheet I'm doing so thank you twice for all your help :D

  5. #5
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Using Index/Match (Equal or less than) with Multiple Criteria

    You're very welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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