+ Reply to Thread
Results 1 to 5 of 5

Index Match if result is not something

  1. #1
    Registered User
    Join Date
    05-14-2012
    Location
    Alaska, USA
    MS-Off Ver
    Excel 2007
    Posts
    31

    Index Match if result is not something

    Hi,
    Does anyone have any ideas on how to do the following:

    When I uncheck a box on my form, I want the formula in a cell to search through a database and return a matching value, and if I check the box, I want it to return a pre-determined value. I have a macro that then adds this data to the database by inserting a row. The database is always updated with my latest entries.

    Here's what I have so far:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The problem is, if I were to check the box and "000" were then entered into the database, then the next time I try to make an entry and I uncheck my box, then my INDEX MATCH formula will return "000", since it wants to find the first match in the list and "000" is on top, so I've lost my options. I need the formula to ignore any result that equals "000" and move on to the next occurence.

    Any ideas?


    Thanks.
    Last edited by Jaron_t; 12-18-2012 at 08:10 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Index Match if result is not something

    Jaron,

    It's a little difficult without having your form to work on, but you can include an IF argument as part of the MATCH function. So you could have: IF your argument is true, INDEX (index range), MATCH(E10,IF(some cell reference is not "000") MATCH range, MATCH type, etc

    I'm not sure if that will help, as I'm unclear where your "000" is coming from (it's been a loooong day), but I hope it points you in the right direction. If the argument logic works and you need a hand with the syntax, let me know.
    Brendan.


    __________________________________________________________________________________________________
    Things to consider:

    1) You can thank any poster by clicking the * at the left of a helpful post.
    2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
    3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.

  3. #3
    Registered User
    Join Date
    05-14-2012
    Location
    Alaska, USA
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Index Match if result is not something

    Not quite right, I don't think. If I follow that logic right, I would still have the same problem in the end, trying to get the formula to skip over to the next result. I could be misunderstanding your suggestion, but to make it clearer I attached an example worksheet. Instead of "000" being the dummy number, I went with "123".
    B1 is search criteria, B3 is result formula. E1:F8 is the database table, and C1 is the linked cell to the checkbox.

    So here's what happens: If I type "a" in B1 and leave the box unchecked, the formula in B3 will return "1". But if I check the box it will return "123". The same goes for any entry, because the "123" value is a constant and is part of the formula. That's how it's supposed to work.

    I run into a problem though if there is a "123" in the table. For example, if I were to search for "s", the formula would return "123" whether I check the box or not, becuase INDEX MATCH finds the first match in the list, and in this case that answer would be "123". What I want the formula to do though, is to skip the "123" result and look for the next occurrence of "s", which would return the answer "4"

    I'm not sure if there is such a thing, but it seems like I should be able to nest some sort of "IF-IS-NOT" argument into the MATCH criteria of the formula. Maybe I need to VBA a funtion for that to work though.example sheet.xlsx
    Last edited by Jaron_t; 12-18-2012 at 05:05 PM.

  4. #4
    Registered User
    Join Date
    05-14-2012
    Location
    Alaska, USA
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Index Match if result is not something

    Ok, I got it figured out. I just needed to use a couple more formulas in different places:

    I made a separate table linked to the first, and qualified the linking formulas to exclude the data I want to ignore. Then I changed my formula to look in the adjusted table rather than the first one. I knew there was a way, just couldn't see it at first. Here is the solution I found in an example worksheet:

    example sheet2.xlsx


    Thanks for any input.

  5. #5
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Index Match if result is not something

    Jaron, I haven't had a chance to look at your file yet, but fair play to you for persevering and finding your own way.

    All the best....

+ 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