+ Reply to Thread
Results 1 to 7 of 7

Putting OR inside a INDEX SMALL formula for multiple criteria, can it be done?

  1. #1
    Registered User
    Join Date
    10-21-2014
    Location
    Wellington, New Zealand
    MS-Off Ver
    2010
    Posts
    19

    Exclamation Putting OR inside a INDEX SMALL formula for multiple criteria, can it be done?

    Hi guys/girls,

    I have been given a formula that works great to a point.

    It accesses a column, and looks up two values in that column (values are referenced in F2 and F3). It then returns the adjacent value in either column A or column B (depending on the formula) for each found instance.

    My problem is that if I delete one of the ref cells (F3 or F2) it returns crazy numbers.

    I would like it to look for both OR one OR the other OR nothing if there is none.

    Any help including thoughts would be great at this stage as deadlines are looming and then clouds will be shrooming!

    Thank you in advance for any help.

    FolloWing is the code and a stripped down sheet with values and formulae needed

    PS. Formula is a ctrl+shift+enter array formula..

    Please Login or Register  to view this content.

    353354d1413915941-index-array-formula-using-multiple-criteria-and-indirect-references-can-it-wo.xlsx

  2. #2
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,891

    Re: Putting OR inside a INDEX SMALL formula for multiple criteria, can it be done?

    Hi,

    There is nothing wrong with your formula at all. The problem is, if you delete F2 or F3, then the cell become empty, and this will be matched all empty cells in the other's sheet.

    So there are two ways :
    1. You change all empty cells in other sheet to non empty (for example, fill with hyphen) --> impractical, almost impossible
    2. Don't let F2 or F3 empty, if you want to exclude this cell, just fill this cell with hypen for example

    Now about the clouds that will be shrooming, you can always bring an umbrella, I would say

  3. #3
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Putting OR inside a INDEX SMALL formula for multiple criteria, can it be done?

    Adjust the formulas to ignore F2 and F3 if they are blank:

    =IFERROR(

    INDEX(INDIRECT($D4&"!A2:A500"),

    SMALL(IF(
    (INDIRECT($D4&"!F2:F500")=$F$2)*($F$2<>"")+
    (INDIRECT($D4&"!F2:F500")=$F$3)*($F$3<>""),

    ROW(INDIRECT($D4&"!F2:F500"))-2+1),

    COUNTIF($D$4:$D4,$D4))),

    "")
    Remember what the dormouse said
    Feed your head

  4. #4
    Registered User
    Join Date
    10-21-2014
    Location
    Wellington, New Zealand
    MS-Off Ver
    2010
    Posts
    19

    Re: Putting OR inside a INDEX SMALL formula for multiple criteria, can it be done?

    TO KAREDOG

    Good times, good times...

    .. I do agree with what you said, as those were my very first thoughts..

    .. And your humour can not go with out a smile, soooo..

    .. Reps for you mate!
    Last edited by whetu; 10-22-2014 at 06:39 AM.

  5. #5
    Registered User
    Join Date
    10-21-2014
    Location
    Wellington, New Zealand
    MS-Off Ver
    2010
    Posts
    19

    Re: Putting OR inside a INDEX SMALL formula for multiple criteria, can it be done?

    TO ROMPERSTOMPER

    This, my friend, is the key to my smiles tonight.

    Wonderful. I will peel it apart next week and leave some explanation for future queries.

    Thank you very much!

    Stars all around tonight!

  6. #6
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,891

    Re: Putting OR inside a INDEX SMALL formula for multiple criteria, can it be done?

    Hi, thanks for the reps, and beside peel the problem, you can also peel an apple. It will keeps the doctor away, you know.

  7. #7
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Putting OR inside a INDEX SMALL formula for multiple criteria, can it be done?

    Glad to help. Please don't forget to mark the thread solved using the Thread Tools link at the top.

+ 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. Replies: 9
    Last Post: 08-29-2014, 09:42 PM
  2. [SOLVED] INDEX MATCH SMALL ROW, Double criteria Lookup
    By GP_SRT in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-22-2013, 11:15 AM
  3. Replies: 6
    Last Post: 10-22-2013, 01:48 PM
  4. [SOLVED] putting $ in the Range which inside .Formula
    By viva2kh in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-18-2012, 04:14 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