Array Formula

[JoeMicro]

Hi:

i have got a sheet that looks similar to this

101


102 P1
102 P2
101 P3
101 P4
103 P5


kl 0
P3 0
P4 0
P5 0
P2 0

i tried this array formula

=MATCH(0,IF(Sheet2!A15:A19=A6,IF(Sheet2!B15:B19=Sheet3!A1:A5,Sheet3!B1:B5,"P")),0)

to do the following.
it should look up in the first range which No is selected (in our case it's 101) then it should look up in range 2 what codes are associated with this No (in our case its {P3,P4}) and then it should lookup in range 3 and give me if there is any 0's in the column next to the corresponding Code.

it doesn't work now because the columns in range 3 are not in the same sorted order.

does anybody have a way around this

i would probably be able to make this work with a macro, but i prefer a formula.

Thanks,
Joe

 

[Fenrirshowl]

Joe

If you are after a formula you would be better off posting in the Office forum. However....

I take it range 3 codes in the left hand column are unique? (But they are unordered)

If that is the case, I would add a column to range 2. Add some MATCH functions bringing through the corresponding values from the second column in range 3 to the values in the second column of range 2.

Once you have this you can either use a SUMPRODUCT function to count the number of zeros in this new last column of range 2 for each value in the first column of range 2 matching the criteria
OR
you could add a further column of AND functions comparing the first column of range 2 with the given value and checking for zeros in the last column and then sum the number of occurrances of the result TRUE.

 

[JoeMicro]

Thanks Fenrirshowl For your reply,

that's a great idea, but i have got some other macros which copy info from that sheet, and i will have to rewrite a lot in order to do as you suggested...

and that was actually going to be my last resort..

i thought i will check around before i do that

Thanks

Joe

 

[AnotherHiggins]

Joe - I'd definitely repost over in forum68. We have a few Array Formula gurus that come up with things that make my head spin. But they aren't looking for those questions here in the VBA forum.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[JoeMicro]

Thanks anotherhiggins & Fenrirshowl

i did as you said