2

I am not new to excel or vba; however, I am having some difficulty with the lack of a boolean "or" function in excel. As you know, the "&" function works fine, but you cannot insert the "|" or "||" operator into a formula. Is there an alternative that will give me an "or"?

Note: The pre-defined "or function" (i.e., [or(condition1, condition 2...)] is not helpful in this instance. It evaluates values in another set of cells rather than the current cell and returns "true" or "false" instead of a numeric value.

The code that I am currently using is the following: =IF(IFERROR(INDEX($H$2:INDIRECT("H"&ROW()-1),MATCH(7,$D$2:INDIRECT("D"&ROW()-1),0)),0)=99,(INDIRECT("F"&ROW())*2),INDIRECT("F"&ROW()))

The value "7" is currently being matched. I need it to match multiple values, such as Match(5|6|7,... or Match (5||6||7,...

Any bright ideas?

  • Match multiple and then return what? Multiple? – Raystafarian Jun 11 '15 at 15:41
  • I don't quite get the general flow of your formula, but if you're trying to match multiple possible values have you ever looked into array formulas? excel-easy.com/functions/array-formulas.html – Steve Taylor Jun 11 '15 at 16:03
  • What you specifically want here cannot be done, ever, in any programming language. A method’s signature is fixed. If it says it accepts a single string as the first argument, that’s it. – Daniel B Jun 11 '15 at 17:04
  • 1
    What do you mean that "& works fine"? In Excel formulas, & is the concatenation operator and has nothing to do with boolean operations. – hBy2Py Jun 11 '15 at 17:11
  • You need to define how you want an "OR" to work here. For your basic MATCH it will find the position of the first instance of 7. For an OR do you want it to find the position of the first instance of any of 5, 6 or 7?......or something else – barry houdini Jun 11 '15 at 19:50
2

This is pretty clunky, but you can nest IFERROR statements inside your INDEX/MATCH.

=INDEX(D:D,IFERROR(MATCH(5,D:D,0),IFERROR(MATCH(6,D:D,0),IFERROR(MATCH(7,D:D,0),""))))

ETA: It works essentially by looking for 5, then 6, then 7 within column D. Barry's solution in the comments for this answer is probably a better answer than this.

  • Welcome to SuperUser! This is by far the most elegant approach I've ever seen to this use case. Nice!! – hBy2Py Jun 11 '15 at 17:09
  • Welcome to Super User! It would be an even better answer if you could provide some explanation how it works. – DavidPostill Jun 11 '15 at 18:53
  • This solves a problem but it's not clear to me whether it solves this problem! That depends on how the OR should work - if 5 and 6 both exist in column D then should the formula return the position of the one that is positioned first? Of course this solution won't do that. If 5 is in D100 and 6 in D2 the MATCH part of the formula will return 100 – barry houdini Jun 11 '15 at 19:53
  • 1
    Assuming Excel 2010 or later you could get the first match for 5, 6 or 7 with this formula: =AGGREGATE(15,6,MATCH({5,6,7},D:D,0),1) – barry houdini Jun 11 '15 at 20:02
  • @barry Nice. Way less kludgy than what I came up with: =INDEX(D:D,MIN(IFERROR(MATCH(5,D:D,0),COUNTA(D:D)+1),IFERROR(MATCH(6,D:D,0),COUNTA(D:D)+1),IFERROR(MATCH(7,D:D,0),COUNTA(D:D)+1))) – Kyle Jun 11 '15 at 20:10
2

Assuming you want to match 5, 6 or 7 and find the first matching row for any of those you can use this formula

=IF(IFERROR(INDEX($H$2:INDIRECT("H"&ROW()-1),AGGREGATE(15,6,MATCH({5,6,7},$D$2:INDIRECT("D"&ROW()-1),0),1)),0)=99,2,1)*INDIRECT("F"&ROW())

AGGREGATE function only exists in Excel 2010 or later versions

This works because the MATCH part now returns an "array" of 3 values - one each for 5, 6 and 7 - either the numerical position in the range, or #N/A if they don't exist in the range, so match might return this result

{#N/A,4,11}

That would mean that 5 isn't found, 6 is found in the 4th row of the lookup range and 7 is found in the 11th row.

The AGGREGATE function, therefore looks like this:

AGGREGATE(15,6,{#N/A,4,11},1)

where 14 is the type of function to apply [SMALL], 6 means it ignores errors and the 1 at the end defines the k value for SMALL, so basically it just takes the smallest value from {#N/A,4,11} while ignoring errors, so in this case it returns 4 which corresponds to the first match for 5, 6 or 7

1

An "a OR b" function is just a combined functions of a "NOT (AND (NOT (a); NOT (b)))" function. It gives the following as a result if you use it like this.

A, TRUE, TRUE, FALSE, FALSE;

B, TRUE, FALSE, TRUE, FALSE;

function, TRUE, TRUE, TRUE, FALSE;

This could be extended to include as many variables as you like but can you use it with a match function is the real question now. Need more details to experiment, and the file if possible.

You could just make a script for this though, to try all the values with a simple loop.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.