Index and Match Formula does not work

[Porterrob]

Hi guys,

Can anyone work out why my Match and Index formula doesn't work on the stock in consignment worksheet.

http://www.box.net/shared/88vj8y987u

The relevent PO's are meant to match up to the part no in the stock in consignment sheet and show how many parts have been moved out from which ever PO. It makes sense when you look at it.

Any help greatly appriciated!

 

[barry houdini]

The result of RIGHT function is always text, you need to convert to numeric to get a number....and a match. Try this formula in C4 copied across and down

=IF(OR(ISERROR(MATCH($A4,'Stock Out'!$A:$A,0)),ISERROR(MATCH(RIGHT(C$3,5)+0,'Stock Out'!$3:$3,0))),"",INDEX('Stock Out'!$1:$1000,MATCH($A4,'Stock Out'!$A:$A,0), MATCH(RIGHT(C$3,5)+0,'Stock Out'!$3:$3,0)))

 

[Porterrob]

Thanks for your help barry,

When I copied the formula across I found that some problems.

1. When I add another PO, e.g I added 84885 to the stock out then 5 pcs of line 4 (AH1-R200-0.1-K2), the consignment stock sheet did not update and was left blank.

2. I also need it to take the total number of parts from each PO for the month, so when I added another 20 pcs of line 24 (PBH-R010-0.5) to 89298 on Stock out for a different date, it didn't add up the total in the Consignement stock sheet. (This could be related to problem 1)

If you can help me out again that will be great!

 

[barry houdini]

I can't replicate those problems the formulas update for me - I added 84885 (as a number) in M3 of Stock Out and then put 5 in M7 is that what you mean? D7 in Consignment picked that value up.....

 

[Porterrob]

That is kinda what I meant, although that did work for me

The problem seems to come is the Consignment doesn't add up values, for example 84885 in M3 of Stock Out and then put 5 in M7, D7 in consignement would pick up that value. However in the next column if you add 84885 in N3 then 10 in N7, D7 in Consignment would still show 5 not 15...

Also some columns are blank on the consignment stock sheet, would that cause any difficulties?

Sorry, Im not used to using Index and Match formulas!