VLOOKUP formula for stock control

Hi all,
I am working on a formula for a stock control spreadsheet. I am trying IF VLOOKUP but it is not working.
I would need a formula in column "product in stock' in components lot register spreadsheet that would say 'NOT AVAILABLE' when for example:
Lot number '100' (D4) for product 'procedure pack' (C4) for 'component 1' (B4) appears in the spreadsheet 'Lot Register' (A:W), and 'AVAILABLE' when 'lot number' does not appear in "lot spreadsheet" and 'released' column in the spreadsheet "components lot register' says YES.

Thanks!!

I'm not sure I follow you completely, but this is the formula I put in cell F4:

=IF(INDEX(Table1[[Component 1]:[Component 22]],MATCH([@Product],Table1[Product],0),MATCH([@Components],Table1[[#Headers],[Component 1]:[Component 22]],0))=[@[Lot Number]],"Not Available","Available")

(NOTE: I had to convert the text under Lot Number to Number to match the other sheet (you could change either one, as long as they match).

So I think you're saying you want the result to be "Not Available" when you find a match, but "Available" if you don't find a match, then you mention about the Release column saying YES, so you don't say what you want if it doesn't match but the release doesn't say YES.

Thanks! Sorry, I may not explain it clearly...
I need the cells in column F (Product in stock) to say NOT AVAILABLE when the Lot Numbers (column D) are entered in Lot Register spreadsheet (which means those lots are being used and not available in stock). And if the lot numbers are not entered in the Lot Register spreadsheet the Product in stock (column F) will be AVAILABLE only if column E (released) states YES.
Does it make sense?

Thanks again!!

It all appears as Not available with that formula.
So if lot number is entered in table 1, and it matches the lot number in table 2, then formula should give "not available", otherwise formula would give 'available' but only if released column says YES.
Does it make sense?

I need the cells in column F (Product in stock) to say NOT AVAILABLE when the Lot Numbers (column D) are entered in Lot Register spreadsheet (which means those lots are being used and not available in stock).

That's what my solution gives you, correct?

And if the lot numbers are not entered in the Lot Register spreadsheet the Product in stock (column F) will be AVAILABLE only if column E (released) states YES.

What do you want it to say if the lot numbers are not entered in the Lot Register spreadsheet the Product in stock (column F) and Column E (released) does NOT say "YES"?

Then it will say AVAILABLE

So you're saying it will say "AVAILABLE" whether or not Column E (released) says "YES", so why even check to see if it says YES? And in this case, my original solution should be giving you what you want.

It doesn't work so far. I think, the problem is that I need to conditions, to be 'available': the first one is that 'released' column needs to say YES and then the 'lot number' in table 2 to not appear in table 1, and 'not available' when 'released' column says NO and/or 'lot number' appears in table 1.

Hi,

Please find attached spreadsheet with the solution.
For testing the formula i have removed lot 100 and 101 from Table1.
I hope the solution meets your requirement.

Regards
Sajal Prasad

Hi Sajal, it works perfectly on the sample template, but when I transfer the formula into my real spreadsheet, it is not working properly. Could that be because my table 1 is in a different excel file and the headings are different? Although I have made changes on the formula to match my real data, it picks the available or not available just as per 'YES" in the 'released from production' column. Any thoughts?

Thanks!!

Unfortunately, I cannot share them (confidential information). But the format is exactly as what I attached earlier with the difference of tables being in different excel spreadsheets and columns headers with different titles. Any ideas about what could fail? Thanks

I think I may need to use a VLOOKUP formula, because I will not have both spreadsheets open all the time.

Hi guys,

I am still working on the formula for my stock register. I have attached the 2 tables to explain myself better. I hope you can help!!

I have 2 tables, the EF 4012 Product Register and the EF 4015 Components, Register. The formula I need is for column E in table EF 4015 Components register.
I need the formula to give me the availability of the components to manufacture the product, so, when component 1 to make product 1 with lot No. 1001 has been released from production (column D), and it hasn't been used for the manufacturing of any other product (that is, it is NOT in table EF 4012), then the component is AVAILABLE (column E, components in stock).
But when the component has not been releaseed from production and/or when the component lot number has been already used (lot number enterd in EF 4012), then colum E components in stock should say NOT AVAILABLE.

I hope this maked sense.

Thansk!!!!

Thanks, JeteMc.

When I put the formula in my real table it all appears as Not available.
I need to have 2 separate tables and I am thinking of removing the product condition so it will be AVAILABLE when column D 'released from production' is YES and the lot number is not on table EF 4012. And NOT AVAILABLE when column D 'release from production' is NO and/or when the lot number has been entered for in table EF 4012.
What do you think?

Thanks again!!

This is the formula I am using applied to my real tables, and it does not seem to work, it shows "not available' for all:

=IF(AND(INDEX('[EF 4012 V1 - rbi2 Lot-Retentions Register.xlsx]EF4012 V1 rbi2 LOT-RETENT REG'!H$146:K$149,MATCH([@Product],'EF 4012 V1 - rbi2 Lot-Retentions Register.xlsx'!Table1[Product],0),MATCH([@Components],'EF 4012 V1 - rbi2 Lot-Retentions Register.xlsx'!Table1[[#Headers],[Component1]:[Component6]],0))=0,D2="Yes"),"Available","Not Available")

I am also trying to remove the condition for 'product', so it will be AVAILABLE when column D 'released from production' is YES and the lot number is not on table EF 4012. And NOT AVAILABLE when column D 'release from production' is NO and/or when the lot number has been entered for in table EF 4012.

Thanks!

So, the formula that could work is the one that would folow: if column D 'release from production' is YES and the component lot number has NOT been entered in table EF 4012, then 'AVAILABLE' and if column D 'released from production' is NO and component lot number has been entered in EF 4012, then is "NOT AVAILABLE".

Does it make sense?

Tables attached.
You are right: column D 'released from production' is NO OR component lot number has been entered in EF 4012, then is "NOT AVAILABLE".

How do i remove the product condition? And, I'd like to keep the headings as they are instead of the A1 references you added previously.

THANKS!!!

Thanks, JeteMc. It works perfectly!!!!

Very much appreciated it!!!!!