Microsoft Office Forums Lookup multiple values and compare different scenarios to get a specific result

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-12-2014, 12:17 AM
mws mws is offline Lookup multiple values and compare different scenarios to get a specific result Windows XP Lookup multiple values and compare different scenarios to get a specific result Office 2007
Novice
Lookup multiple values and compare different scenarios to get a specific result
 
Join Date: Apr 2014
Posts: 3
mws is on a distinguished road
Default Lookup multiple values and compare different scenarios to get a specific result

In the attached sheet I want formula in column C that will give the new order quantity with following logic;

If ("Product"="Product")of column A & I, ("Closing Stock" of a particular product<"ROL" of that product) & (Current "Date" (say date in B5 if formula is being applied in C5)-Any latest previous "New Order" Date>="Lead Time" of that product)



Means if it has given new order quantity once then it should not give it again within the lead time even though the closing stock is less than the ROL. For example if it gives 150 in C3 then 150 comes on or after 21.03.2014.

Our formula should perform the 3rd logic only if it finds any new order qty in Array C otherwise first 2 logic are enough.

Thanks in anticipation.
Attached Files
File Type: xlsx Sample Sheet.xlsx (11.1 KB, 6 views)
Reply With Quote
  #2  
Old 04-13-2014, 10:55 PM
mws mws is offline Lookup multiple values and compare different scenarios to get a specific result Windows XP Lookup multiple values and compare different scenarios to get a specific result Office 2007
Novice
Lookup multiple values and compare different scenarios to get a specific result
 
Join Date: Apr 2014
Posts: 3
mws is on a distinguished road
Default

Is any body there who can help me out of the above mentioned issue.
Reply With Quote
  #3  
Old 04-23-2014, 06:07 AM
gebobs gebobs is offline Lookup multiple values and compare different scenarios to get a specific result Windows 7 64bit Lookup multiple values and compare different scenarios to get a specific result Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 832
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

Let me take a look at it.
Reply With Quote
  #4  
Old 04-23-2014, 07:36 AM
gebobs gebobs is offline Lookup multiple values and compare different scenarios to get a specific result Windows 7 64bit Lookup multiple values and compare different scenarios to get a specific result Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 832
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

Oy...at first blush, I think this is beyond my Excel skills. The first part, comparing the Closing Stock to ROP can be done by...

=IF([Closing Stock]<VLOOKUP([Product],[Product Data Table],2)

But it's this next part that is stumping me:
  • The maximum Date
  • For the specified Product
  • Where the Date is less than the Date on the specified line
  • And Where New Order > 0
This would be easy peasy for me in Access, but in Excel it's beyond my meager skills. Maybe someone can figure out how to do this with the Reference functions or it might require a coded function. Or maybe I'm overcomplicating it. HELP!!!
Reply With Quote
  #5  
Old 04-23-2014, 12:01 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Lookup multiple values and compare different scenarios to get a specific result Windows 7 64bit Lookup multiple values and compare different scenarios to get a specific result Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,397
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

I suppose that if you did not get answers, your problem is not clear. Please post a sample sheet showing BEFORE and AFTER ( personally, I don't understand your requirements)
__________________
Please note I am not active on this forum anymore
Reply With Quote
  #6  
Old 05-24-2014, 04:52 AM
mws mws is offline Lookup multiple values and compare different scenarios to get a specific result Windows XP Lookup multiple values and compare different scenarios to get a specific result Office 2007
Novice
Lookup multiple values and compare different scenarios to get a specific result
 
Join Date: Apr 2014
Posts: 3
mws is on a distinguished road
Default

The problem worked out finally. Sample Sheet attached.
However thanks for all the support.
Attached Files
File Type: xlsx Solution.xlsx (20.0 KB, 2 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel - search Multiple Values (HELP!) duskdjl Excel 4 03-28-2013 01:11 AM
How can match or lookup values from two separate tables? klawk26 Excel 1 07-31-2012 09:04 PM
Lookup multiple values and compare different scenarios to get a specific result How can I retreive multiple records using a lookup? jrpey Excel 3 11-14-2011 02:10 PM
Lookup multiple values and compare different scenarios to get a specific result How to count multiple values in a single cell, except zero? iuliandonici Excel 1 04-13-2011 09:45 PM
Lookup multiple values and compare different scenarios to get a specific result Sum & difference between multiple values in a single cell iuliandonici Excel 4 04-13-2011 09:27 PM


All times are GMT -7. The time now is 05:11 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft