# VLOOKUP formula for stock control

1. ## 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!!

2. ## Re: VLOOKUP formula for stock control

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.

3. ## Re: VLOOKUP formula for stock control

Try in F4:
``Please Login or Register  to view this content.``

4. ## Re: VLOOKUP formula for stock control

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!!

5. ## Re: VLOOKUP formula for stock control

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"?

6. ## Re: VLOOKUP formula for stock control

Then it will say AVAILABLE

7. ## Re: VLOOKUP formula for stock control

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.

8. ## Re: VLOOKUP formula for stock control

I interprete like this:

is it true?

That is my solution in #3.

9. ## Re: VLOOKUP formula for stock control

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?

10. ## Re: VLOOKUP formula for stock control

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.

11. ## Re: VLOOKUP formula for stock control

Hi,

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

Regards

12. ## Re: VLOOKUP formula for stock control

Sorry for off-topic interjection:

@Sajal

ONCE AGAIN ...

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

13. ## Re: VLOOKUP formula for stock control

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!!

14. ## Re: VLOOKUP formula for stock control

We might be able to offer better assistance if we could see a sample of the file containing the table and the file containing the formula.
Let us know if you have any questions.

15. ## Re: VLOOKUP formula for stock control

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

16. ## Re: VLOOKUP formula for stock control

If what you say about your real data and the sample data is true, then just changing the references should work.

Do you have both workbooks open? I have not opened Sajal's workbook, and as he has refused to add the detail I requested to his post, I have no idea what the formula is that he suggested. I suggest that you post his formula and your tweaked version of it so that we can try and spot what may be causing the problem.

If there is no issue with this, then I am afraid there must be something fundamentally different between the sample data and the real data (e.g. text that looks like numbers in one but not the other, or dates likewise).

17. ## Re: VLOOKUP formula for stock control

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

18. ## Re: VLOOKUP formula for stock control

OK - well, that statement addresses one of the things I mentioned. What about the rest? If this were to do with the workbook not being open and needing to be, i believe you'd get a #REF! error - is that what yo are getting? It's not what you seemed to be saying earlier.

You aren't giving very much here for us to go on - it's a bit like trying to diagnose a wasp sting over the phone with someone just saying, "It hurts".

19. ## Re: VLOOKUP formula for stock control

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!!!!

20. ## Re: VLOOKUP formula for stock control

I put both tables in the EF 4015 workbook for simplicity.
A row 1 has been inserted over the 4012 table and populated using: =RIGHT(B2,LEN(B2)-FIND("C",B2)+1)
The formula for column E of the 4015 table is: =IF(AND(INDEX('EF 4012'!B\$3:K\$6,MATCH(B2,'EF 4012'!A\$3:A\$6,0),MATCH(A2,'EF 4012'!B\$1:K\$1,0))=0,D2="Yes"),"Available","Not Available")
Note: If the tables must be in different workbooks then 'EF 4012'! will need to be preceded by the path of the EF 4012 workbook.
Let us know if you have any questions.

21. ## Re: VLOOKUP formula for stock control

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!!

22. ## Re: VLOOKUP formula for stock control

Did you try adding the file name to the formula? i.e.:
Formula:
`Please Login or Register  to view this content.`

Make sure that the EF 4012 Product register.xlsx‎ file is open.
Let us know if you have any questions.

23. ## Re: VLOOKUP formula for stock control

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!

24. ## Re: VLOOKUP formula for stock control

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?

25. ## Re: VLOOKUP formula for stock control

I was under the impression that if column D 'released from production' is NO OR component lot number has been entered in EF 4012, then is "NOT AVAILABLE".
Formula:
`Please Login or Register  to view this content.`

The portions in red don't wrap the file name in square brackets and you may want to use A1 references as opposed to structured references in both instances.
In the event that doesn't help and looking at the files that are attached to post #1, it does not appear that there would be sensitive information such as names in the actual files. Would it be possible to attach a small sample of each file so that we can attempt to trouble shoot the formula in post #23?
Let us know if you have any questions.

26. ## Re: VLOOKUP formula for stock control

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!!!

27. ## Re: VLOOKUP formula for stock control

The following yields the same results as those that are given manually in column E:
Formula:
`Please Login or Register  to view this content.`

Let us know if you have any questions.

28. ## Re: VLOOKUP formula for stock control

Thanks, JeteMc. It works perfectly!!!!

Very much appreciated it!!!!!

29. ## Re: VLOOKUP formula for stock control

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1