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

1. ## 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. ## 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. ## 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))),

"")

4. ## 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!

5. ## 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. ## 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. ## Re: Putting OR inside a INDEX SMALL formula for multiple criteria, can it be done?

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

#### 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