Array Formula

[JoeMicro]

I Posted This In the VBA Forum

http://www.tek-tips.com/viewthread.cfm?qid=1448196&page=1

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

 

[ck1999]

try

=INDEX($A$13:$B$17,MATCH(INDEX($A$4:$B$8,SMALL(IF($A$4:$B$8=$A$1,ROW($A$4:$B$8)),ROW(1:1))-3,2),$A$13:$A$17,0),2)
[/code

once enter hit ctrl+shift+enter

then copy and paste down the column

$A$4:$B:$8 is your table 2

$A$1 is your No

$A$13:$A$17 is your table 3

With your data I copied in down 4 cell 1st two were 0 last two were #num since there were only 2 results in table 3.  I hope this helps your  Your can possibly add an  error trap to remove the #num
 if(iserror(xxxx),"",(xxxx)  
where xxxx is the code above. Did not test this!

ck1999

 

[JoeMicro]

Thank ck1999 For you reply
it looks i was misunderstood..

my tables are on three different sheets, and the formula needs to be on the sheet with the first range, where the numbers are user input, range tow could be modified, and range 3 column A shouldn't be modified, and column B varies.

so i need to let the user know when he types in a no. weather the any of the codes corresponding to the no., is 0

i hope now i m more clear (or m i more confusing?)

so before i m using this on the multiple sheet i m just trying to get this done in a small table

thanks again
Joe

 

[ck1999]

I do not think I misunderstood. I tested in on 1 sheet not three and thought you could enter your ranges

try

=IF(INDEX(Sheet3!$A$1:$B$5,MATCH(INDEX(Sheet2!$A$15:$B$19,SMALL(IF(Sheet2!$A$15:$B$19=$A$1,ROW(Sheet2!$A$15:$A$19)),ROW(1:1))-14,2),Sheet3!$A$1:$A$5,0),2)=0,"Has Zero","No Zero")

If the no designated in a1 has matching P? in table 2 and the p? that correlated in table 2 is listed in table 3 and has a "0" then you will get a "Has Zero" if in table 3 the p? is not found to have a zero then you should get a "no zero"

However, if as in your case 101 has both p3 and p4 in table 2. You have to have the formula copied to another row. 1 row will display the p3 the next row will have the p4 data. If in table 3 there in not a p3 then you will get an #NA if p3 is in the able then you will get a "Has Zero" if there is a corresponding 0 in col b. If in col B you have somthing besides a 0 you should get a "No Zero" returned.

ck1999

 

[ck1999]

Just for clarification

What else would be in column 2 of table 3 if it did not contain a zero?

What results are you looking for a "Yes" or "No" to be displayed or to return the sum of column 2 in table 3?

ck1999

 

[combo]

What answer do you expect in case of two values that match?
I'd rather either start from the end: add third column to the first table that contains corresponding values from the second table and next use autofilter.
Another option is to separate data in new workbook, that contains two tables on two sheets. Next with external data query join them and with pivot table (can be done in one go) extract data.

combo

 

[JoeMicro]

Thanks Guys for trying to help

for ck1999

However, if as in your case 101 has both p3 and p4 in table 2. You have to have the formula copied to another row.

that's exactly y i thought i was misunderstood.
because in table A the use could enter a few rows of no like

101
102
103
104
301
302

so i need i 1 offset row to display if "Any" of the corresponding P's have a 0.

and for your second Question, column 2 of table three could have any number, and i need to give the user info if its less then 0.

i will give you a brief explanation y i do it, its no secret
my bus has a production business and before a production is made the manager is going to enter the item no of the items being produced (Ex: 101,102,103)
and i need to tell him if there is stock of the Raw Material.

so sheet 1 is the user input
sheet 2 is the ingredients for each item
(Ex: 101, P1
101, P2
101, P3
102, P2
102, P3)
sheet 3 is a list of the details of the Raw Material cost In Stock Etc.

So I hope this Clarifies Things

Thanks
Joe