Index function error

[Motty]

Hi all,

I have a problem with an index function i created. Here is what the function looks like: =INDEX(SMALL(C3:C782,{1;2;3;4;5}),OR(MATCH(1,A3:A782=E7,0),MATCH(1,B3:B782=E7,0)))

The intended purpose with it is to find the 5 smallest values from cells C3:C782 if cells A3:A782 MATCH E7 OR if cells B3:B782 Match E7.

Hope that makes sense if not i will be happy to elaborate. At the moment i get error #N/A with my statement.

Thanks in advance for any help,

Motty.

[Bernard Liengme]

A very complex formula; not sure what it is trying to do.
I played with it with some dummy data (only row 3 to 16)
1) it must be entered with CRTL+SHIFT+ENTER as it is an array formula)
2) you must coerce the Boolean values to 0/1; I used double negation
=INDEX(SMALL(C3:C16,{1;2;3;4;5}),OR(MATCH(1,--(A3:A16=E7),0),MATCH(1,--(B3:B16=E7),0)))
Not sure if it does what you want but I got a numeric value.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Motty" <[email protected]> wrote in
message news:[email protected]...
>
> Hi all,
>
> I have a problem with an index function i created. Here is what the
> function looks like:
> *=INDEX(SMALL(C3:C782,{1;2;3;4;5}),OR(MATCH(1,A3:A782=E7,0),MATCH(1,B3:B782=E7,0)))*
>
> The intended purpose with it is to find the 5 smallest values from
> cells C3:C782 if cells A3:A782 MATCH E7 OR if cells B3:B782 Match E7.
>
> Hope that makes sense if not i will be happy to elaborate. At the
> moment i get error #N/A with my statement.
>
> Thanks in advance for any help,
>
> Motty.
>
>
> --
> Motty
> ------------------------------------------------------------------------
> Motty's Profile:
> http://www.excelforum.com/member.php...o&userid=29233
> View this thread: http://www.excelforum.com/showthread...hreadid=490551
>

[JE McGimpsey]

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

D1:D5: =SMALL(IF((A3:A786=E7)+(B3:B786=E7),C3:C786),{1;2;3;4;5})

In article <[email protected]>,
Motty <[email protected]> wrote:

> Hi all,
>
> I have a problem with an index function i created. Here is what the
> function looks like:
> *=INDEX(SMALL(C3:C782,{1;2;3;4;5}),OR(MATCH(1,A3:A782=E7,0),MATCH(1,B3:B782=E7
> ,0)))*
>
> The intended purpose with it is to find the 5 smallest values from
> cells C3:C782 if cells A3:A782 MATCH E7 OR if cells B3:B782 Match E7.
>
> Hope that makes sense if not i will be happy to elaborate. At the
> moment i get error #N/A with my statement.
>
> Thanks in advance for any help,
>
> Motty.

[Domenic]

Try...

=SMALL(IF((A3:A782=E7)+(B3:B782=E7),C3:C782),1)

....confirmed with CONTROL+SHIFT+ENTER. Change the formula accordingly
for the second smallest, third, fourth, and fifth.

Hope this helps!

In article <[email protected]>,
Motty <[email protected]> wrote:

> Hi all,
>
> I have a problem with an index function i created. Here is what the
> function looks like:
> *=INDEX(SMALL(C3:C782,{1;2;3;4;5}),OR(MATCH(1,A3:A782=E7,0),MATCH(1,B3:B782=E7
> ,0)))*
>
> The intended purpose with it is to find the 5 smallest values from
> cells C3:C782 if cells A3:A782 MATCH E7 OR if cells B3:B782 Match E7.
>
> Hope that makes sense if not i will be happy to elaborate. At the
> moment i get error #N/A with my statement.
>
> Thanks in advance for any help,
>
> Motty.

[Motty]

Hi all,

Thanks for the replies all were very useful I ended up using JE McGimpsey formula as it does exactley what i wanted, the things is the value E7 changes when you want to copy and paste so moving to E8, E9 and so on, which is what i wanted. Thanks for the help guys very much appreciated,


Motty.