+ Reply to Thread
Results 1 to 29 of 29

VLOOKUP formula for stock control

  1. #1
    Registered User
    Join Date
    02-25-2019
    Location
    Adelaide, Australia
    MS-Off Ver
    365
    Posts
    43

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

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    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.
    Attached Files Attached Files

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: VLOOKUP formula for stock control

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

  4. #4
    Registered User
    Join Date
    02-25-2019
    Location
    Adelaide, Australia
    MS-Off Ver
    365
    Posts
    43

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

  5. #5
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    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. #6
    Registered User
    Join Date
    02-25-2019
    Location
    Adelaide, Australia
    MS-Off Ver
    365
    Posts
    43

    Re: VLOOKUP formula for stock control

    Then it will say AVAILABLE

  7. #7
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    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. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: VLOOKUP formula for stock control

    I interprete like this:

    If "Not found" and "Yes", "Not AVAILABLE", else, "AVAILABLE"
    is it true?

    That is my solution in #3.

  9. #9
    Registered User
    Join Date
    02-25-2019
    Location
    Adelaide, Australia
    MS-Off Ver
    365
    Posts
    43

    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. #10
    Registered User
    Join Date
    02-25-2019
    Location
    Adelaide, Australia
    MS-Off Ver
    365
    Posts
    43

    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. #11
    Registered User
    Join Date
    06-19-2020
    Location
    Pune, India
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: VLOOKUP formula for stock control

    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
    Attached Files Attached Files

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  13. #13
    Registered User
    Join Date
    02-25-2019
    Location
    Adelaide, Australia
    MS-Off Ver
    365
    Posts
    43

    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. #14
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  15. #15
    Registered User
    Join Date
    02-25-2019
    Location
    Adelaide, Australia
    MS-Off Ver
    365
    Posts
    43

    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. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    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. #17
    Registered User
    Join Date
    02-25-2019
    Location
    Adelaide, Australia
    MS-Off Ver
    365
    Posts
    43

    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. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    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. #19
    Registered User
    Join Date
    02-25-2019
    Location
    Adelaide, Australia
    MS-Off Ver
    365
    Posts
    43

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

  20. #20
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    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.
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    02-25-2019
    Location
    Adelaide, Australia
    MS-Off Ver
    365
    Posts
    43

    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. #22
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: VLOOKUP formula for stock control

    Did you try adding the file name to the formula? i.e.:
    Formula: copy to clipboard
    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. #23
    Registered User
    Join Date
    02-25-2019
    Location
    Adelaide, Australia
    MS-Off Ver
    365
    Posts
    43

    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. #24
    Registered User
    Join Date
    02-25-2019
    Location
    Adelaide, Australia
    MS-Off Ver
    365
    Posts
    43

    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. #25
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    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: copy to clipboard
    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. #26
    Registered User
    Join Date
    02-25-2019
    Location
    Adelaide, Australia
    MS-Off Ver
    365
    Posts
    43

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

  27. #27
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: VLOOKUP formula for stock control

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

    Let us know if you have any questions.

  28. #28
    Registered User
    Join Date
    02-25-2019
    Location
    Adelaide, Australia
    MS-Off Ver
    365
    Posts
    43

    Re: VLOOKUP formula for stock control

    Thanks, JeteMc. It works perfectly!!!!

    Very much appreciated it!!!!!

  29. #29
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 31
    Last Post: 10-28-2015, 01:54 AM
  2. [SOLVED] Stock list using Vlookup, need the price field to read as TBA is stock on hand is 0
    By meerabell in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-31-2015, 07:42 AM
  3. Formula for stock control needed
    By Su3b3a in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-30-2013, 07:04 AM
  4. [SOLVED] Stock Control - Alert user to low stock levels.
    By Kaies in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-29-2013, 08:56 AM
  5. Stock Control
    By gks1968 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-03-2013, 12:44 AM
  6. Stock Control Formula
    By andy4974 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-03-2012, 04:59 PM
  7. Stock control
    By beseda2004 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-03-2012, 03:52 AM

Bookmarks

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