+ Reply to Thread
Results 1 to 8 of 8

Lookup based on Multiple Criteria on Multiple Table

  1. 02-03-2017, 09:28 AM #1
    oktovan
    oktovan is offline
    Registered User
    Join Date
    02-01-2017
    Location
    jakarta
    MS-Off Ver
    2016
    Posts
    8

    Lookup based on Multiple Criteria on Multiple Table

    Hi everyone!

    I was wondering if someone could help me figure out the formula for the following attached files.
    It's about FIFO table calculation that lookup from another table with multiple criteria.
    - ID_OUT will always be shown until stock out remaining = 0
    - ID_IN automatic choose using reference : PN and STOCK_IN_REMAINING <> 0


    Thanks for help !
    Attached Images Attached Images
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-03-2017, 03:46 PM #2
    oktovan
    oktovan is offline
    Registered User
    Join Date
    02-01-2017
    Location
    jakarta
    MS-Off Ver
    2016
    Posts
    8

    Re: Lookup based on Multiple Criteria on Multiple Table

    UP...Up....
    Reply With QuoteRegister To Reply
  3. 02-03-2017, 11:42 PM #3
    oktovan
    oktovan is offline
    Registered User
    Join Date
    02-01-2017
    Location
    jakarta
    MS-Off Ver
    2016
    Posts
    8

    Re: Lookup based on Multiple Criteria on Multiple Table

    no body can't help?
    Reply With QuoteRegister To Reply
  4. 02-04-2017, 03:38 AM #4
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Lookup based on Multiple Criteria on Multiple Table

    I use other format for In_out

    See attachment. Is it possible?
    Attached Files Attached Files
    Quang PT
    Reply With QuoteRegister To Reply
  5. 02-04-2017, 07:01 AM #5
    oktovan
    oktovan is offline
    Registered User
    Join Date
    02-01-2017
    Location
    jakarta
    MS-Off Ver
    2016
    Posts
    8

    Re: Lookup based on Multiple Criteria on Multiple Table

    Hi Bebo021999

    actually i cannot use your format. I still have to use database friendly format.
    my biggest problem right now is i keep getting circular reference error when try to attach the available ID_IN to ID_OUT.
    i already try using iterative circulation, but sill not get the right result.
    Reply With QuoteRegister To Reply
  6. 02-04-2017, 04:08 PM #6
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,563

    Re: Lookup based on Multiple Criteria on Multiple Table

    This proposed solution employs three helper columns that could be hidden and/or moved for aesthetic purposes. There are too many formulas involved to make a full discussion practical, however the three formulas that populate the helper columns (Q,R*,S*) respectively are:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    These provide the data for the formula that populates column L*:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    *Indicates an array entered formulas which are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  7. 02-04-2017, 10:28 PM #7
    oktovan
    oktovan is offline
    Registered User
    Join Date
    02-01-2017
    Location
    jakarta
    MS-Off Ver
    2016
    Posts
    8

    Re: Lookup based on Multiple Criteria on Multiple Table

    Wow.. you are genius JeteMc... It's just like what i need...

    Thank youuu...
    Reply With QuoteRegister To Reply
  8. 02-05-2017, 04:04 PM #8
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,563

    Re: Lookup based on Multiple Criteria on Multiple Table

    You're Welcome. Thank you for the feedback and marking the thread as 'Solved'. I hope that you have a blessed day.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to Lookup value with multiple criteria in Excel table
    By herukuncahyono in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-20-2015, 11:41 PM
  2. Combining multiple cell content based on multiple criteria (IF, LOOKUP, INDEX??)
    By MacWatts in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-20-2014, 04:43 PM
  3. [SOLVED] Lookup and sum multiple values based on a single criteria in multiple sheets.
    By paulsanett in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2014, 12:18 PM
  4. [SOLVED] Table Lookup - Multiple Criteria
    By jdw001 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-08-2012, 04:56 AM
  5. [SOLVED] Lookup from a table with multiple criteria
    By some99 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-06-2012, 02:00 PM
  6. Multiple Criteria Lookup in Table
    By Grimace in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-11-2012, 03:21 AM
  7. Lookup based on multiple criteria
    By day92 in forum Excel General
    Replies: 3
    Last Post: 03-15-2011, 11:59 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1