OR function
 

Is there a way to shorten or compress the "OR" portion of this formula

=IF(OR(C5=N61,C5=N62,C5=N63,C5=N64,C5=N65,C5=N66,C 5=N67,C5=N68,C5=N69,C5=N70,C5=N71,C5=N72,C5=N73,C5 =N74,C5=N75,C5=N76,C5=N77,C5=N78,C5=N79,C5=N80,C5= N81,C5=N82,C5=N83),VLOOKUP(C5,Matrix!$A$2:$AB$28,M ATCH(B5,Matrix!$A$1:$AB$1,0),FALSE),Q60)

try
=if(match(c5,n61:n83,0)),VLOOKUP(C5,Matrix!$A$2:$A B$28,MATCH(B5,Matrix!$A$1:$AB$1,0),FALSE),Q60)

"Larry" wrote:

Is there a way to shorten or compress the "OR" portion of this formula

=IF(OR(C5=N61,C5=N62,C5=N63,C5=N64,C5=N65,C5=N66,C 5=N67,C5=N68,C5=N69,C5=N70,C5=N71,C5=N72,C5=N73,C5 =N74,C5=N75,C5=N76,C5=N77,C5=N78,C5=N79,C5=N80,C5= N81,C5=N82,C5=N83),VLOOKUP(C5,Matrix!$A$2:$AB$28,M ATCH(B5,Matrix!$A$1:$AB$1,0),FALSE),Q60)

=if(sumproduct(--(n61:n83=c5))=1,VLOOKUP(C5,Matrix!$A$2:$AB$28,MAT CH(B5,Matrix!$A$1:$AB$1,0),0),Q60)


"Larry" wrote in message
...
Is there a way to shorten or compress the "OR" portion of this formula

=IF(OR(C5=N61,C5=N62,C5=N63,C5=N64,C5=N65,C5=N66,C 5=N67,C5=N68,C5=N69,C5=N70,C5=N71,C5=N72,C5=N73,C5 =N74,C5=N75,C5=N76,C5=N77,C5=N78,C5=N79,C5=N80,C5= N81,C5=N82,C5=N83),VLOOKUP(C5,Matrix!$A$2:$AB$28,M ATCH(B5,Matrix!$A$1:$AB$1,0),FALSE),Q60)

Maybe this?

=IF(ISNA(VLOOKUP(C5,N61:N83,1,FALSE)),Q60,VLOOKUP( C5,Matrix!$A$2:$AB$28,MATCH(B5,Matrix!$A$1:$AB$1,0 ),FALSE))

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Larry" wrote in message
...
Is there a way to shorten or compress the "OR" portion of this formula

=IF(OR(C5=N61,C5=N62,C5=N63,C5=N64,C5=N65,C5=N66,C 5=N67,C5=N68,C5=N69,C5=N70,C5=N71,C5=N72,C5=N73,C5 =N74,C5=N75,C5=N76,C5=N77,C5=N78,C5=N79,C5=N80,C5= N81,C5=N82,C5=N83),VLOOKUP(C5,Matrix!$A$2:$AB$28,M ATCH(B5,Matrix!$A$1:$AB$1,0),FALSE),Q60)

It certainly is

=IF(ISNUMBER(MATCH(C5,N61:N83,0)),VLOOKUP(C5,Matri x!$A$2:$AB$28,MATCH(B5,Matrix!$A$1:$AB$1,0),FALSE) ,Q60)


Regards,

Peo Sjoblom

"Larry" wrote:

Is there a way to shorten or compress the "OR" portion of this formula

=IF(OR(C5=N61,C5=N62,C5=N63,C5=N64,C5=N65,C5=N66,C 5=N67,C5=N68,C5=N69,C5=N70,C5=N71,C5=N72,C5=N73,C5 =N74,C5=N75,C5=N76,C5=N77,C5=N78,C5=N79,C5=N80,C5= N81,C5=N82,C5=N83),VLOOKUP(C5,Matrix!$A$2:$AB$28,M ATCH(B5,Matrix!$A$1:$AB$1,0),FALSE),Q60)

This works perfectly. Help me to understand. Why ISNUMBER? MATCH returns a
relative row or column number,right? The cells N61:N83 and B5, C5 contain
text.

"Peo Sjoblom" wrote:

It certainly is

=IF(ISNUMBER(MATCH(C5,N61:N83,0)),VLOOKUP(C5,Matri x!$A$2:$AB$28,MATCH(B5,Matrix!$A$1:$AB$1,0),FALSE) ,Q60)


Regards,

Peo Sjoblom

"Larry" wrote:

Is there a way to shorten or compress the "OR" portion of this formula

=IF(OR(C5=N61,C5=N62,C5=N63,C5=N64,C5=N65,C5=N66,C 5=N67,C5=N68,C5=N69,C5=N70,C5=N71,C5=N72,C5=N73,C5 =N74,C5=N75,C5=N76,C5=N77,C5=N78,C5=N79,C5=N80,C5= N81,C5=N82,C5=N83),VLOOKUP(C5,Matrix!$A$2:$AB$28,M ATCH(B5,Matrix!$A$1:$AB$1,0),FALSE),Q60)

Yes match will return a relative row number if there is a match, thus if a
row number is returned one of the cells will match C5 and isnumber (row
number) is just to return TRUE or FALSE otherwise if not found it will
return #N/A so it basically tests for that any of the cells in N61:N83
holds the value in C5

--
Regards,

Peo Sjoblom


"Larry" wrote in message
...
This works perfectly. Help me to understand. Why ISNUMBER? MATCH
returns a
relative row or column number,right? The cells N61:N83 and B5, C5 contain
text.

"Peo Sjoblom" wrote:

It certainly is

=IF(ISNUMBER(MATCH(C5,N61:N83,0)),VLOOKUP(C5,Matri x!$A$2:$AB$28,MATCH(B5,Matrix!$A$1:$AB$1,0),FALSE) ,Q60)


Regards,

Peo Sjoblom

"Larry" wrote:

Is there a way to shorten or compress the "OR" portion of this formula

=IF(OR(C5=N61,C5=N62,C5=N63,C5=N64,C5=N65,C5=N66,C 5=N67,C5=N68,C5=N69,C5=N70,C5=N71,C5=N72,C5=N73,C5 =N74,C5=N75,C5=N76,C5=N77,C5=N78,C5=N79,C5=N80,C5= N81,C5=N82,C5=N83),VLOOKUP(C5,Matrix!$A$2:$AB$28,M ATCH(B5,Matrix!$A$1:$AB$1,0),FALSE),Q60)

Larry

Another option:

=IF(OR(C5=N61:N83),VLOOKUP(C5,Matrix!$A$2:$AB$28,M ATCH(B5,Matrix!$A$1:$AB$1,0),FALSE),Q60)

Since this is an array formula, it must be entered with
<Shift<Ctrl<Enter,
also if edited later.
--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Larry" skrev i en meddelelse
...
Is there a way to shorten or compress the "OR" portion of this formula

=IF(OR(C5=N61,C5=N62,C5=N63,C5=N64,C5=N65,C5=N66,C 5=N67,C5=N68,C5=N69,C5=N70,C5=N71,C5=N72,C5=N73,C5 =N74,C5=N75,C5=N76,C5=N77,C5=N78,C5=N79,C5=N80,C5= N81,C5=N82,C5=N83),VLOOKUP(C5,Matrix!$A$2:$AB$28,M ATCH(B5,Matrix!$A$1:$AB$1,0),FALSE),Q60)