Need a lookup formula that matches 2 values and returns the 3rd va

I neeed to merge 16000 rows of data with some duplicate vales. Poor record 
keeping lead to two lists and each was updated independantly if at all. Some 
orders show up one list and not the other so I can't just sort or do a dup 
search. I'm trying to recreate this data and track the status of all orders 
for the past 12 months. When orders were shipped out in parts the same order 
number was used and this has been a nightmare trying to sort the duplicates 
and match the dates.
I need a formula for F that returns the current status based on the order 
number and date. I tried a simple vlookup, but it returns the same status for 
each order #. I need the order status based on number and date. Please HELP.
   A          B                C            D                E               
        F
Order #   Date           Order #   Date	Status                
433548    4/29/2009   433548    4/29/2009	On hold
433548    5/7/2009     433548    5/7/2009	Shipped in part
433548    5/9/2009     433548    5/9/2009	Shipped full
433601    5/7/2009     433548    5/7/2009	Shipped in part
433665    5/7/2009     433548    5/7/2009	Shipped in part
519080    5/8/2009     433548    5/8/2009	On hold
519080    5/9/2009     433548    5/9/2009	Shipped full

0
Utf
2/18/2010 4:46:02 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
1049 Views

Similar Articles

[PageSpeed] 50

It is too difficult [for me] to understand what you are after.
As there are only a few orders in you example - would you be so kind to 
present [typed by hand] the col. F results !?
Micky



"HFST04" wrote:

> I neeed to merge 16000 rows of data with some duplicate vales. Poor record 
> keeping lead to two lists and each was updated independantly if at all. Some 
> orders show up one list and not the other so I can't just sort or do a dup 
> search. I'm trying to recreate this data and track the status of all orders 
> for the past 12 months. When orders were shipped out in parts the same order 
> number was used and this has been a nightmare trying to sort the duplicates 
> and match the dates.
> I need a formula for F that returns the current status based on the order 
> number and date. I tried a simple vlookup, but it returns the same status for 
> each order #. I need the order status based on number and date. Please HELP.
>    A          B                C            D                E               
>         F
> Order #   Date           Order #   Date	Status                
> 433548    4/29/2009   433548    4/29/2009	On hold
> 433548    5/7/2009     433548    5/7/2009	Shipped in part
> 433548    5/9/2009     433548    5/9/2009	Shipped full
> 433601    5/7/2009     433548    5/7/2009	Shipped in part
> 433665    5/7/2009     433548    5/7/2009	Shipped in part
> 519080    5/8/2009     433548    5/8/2009	On hold
> 519080    5/9/2009     433548    5/9/2009	Shipped full
> 
0
Utf
2/18/2010 6:26:01 PM
I need col F to return the status "On Hold, Shipped in Part, Shipped in full" 
by matching the order # and date in col C and D to col A and B. 
What I'm looking for is a formula that says if A1 (433548) = C1 (433548) and 
B1(4/29/2009) = D1 (4/29/2009) then F = On Hold. If A2 (433548) = C2 (433548) 
and B2(5/7/2009) = D (5/7/2009) then F = Shipped in part.
The problem i have with my vlookup is it rerutrns the same Status (On Hold 
etc) for each order number, but I need the status based on order number and 
date.
Thank you
A             B                C            D                E
433548    4/29/2009   433548    4/29/2009	On hold
433548    5/7/2009     433548    5/7/2009	Shipped in part
433548    5/9/2009     433548    5/9/2009	Shipped full


"מיכאל (מיקי) אבידן" wrote:

> It is too difficult [for me] to understand what you are after.
> As there are only a few orders in you example - would you be so kind to 
> present [typed by hand] the col. F results !?
> Micky
> 
> 
> 
> "HFST04" wrote:
> 
> > I neeed to merge 16000 rows of data with some duplicate vales. Poor record 
> > keeping lead to two lists and each was updated independantly if at all. Some 
> > orders show up one list and not the other so I can't just sort or do a dup 
> > search. I'm trying to recreate this data and track the status of all orders 
> > for the past 12 months. When orders were shipped out in parts the same order 
> > number was used and this has been a nightmare trying to sort the duplicates 
> > and match the dates.
> > I need a formula for F that returns the current status based on the order 
> > number and date. I tried a simple vlookup, but it returns the same status for 
> > each order #. I need the order status based on number and date. Please HELP.
> >    A          B                C            D                E               
> >         F
> > Order #   Date           Order #   Date	Status                
> > 433548    4/29/2009   433548    4/29/2009	On hold
> > 433548    5/7/2009     433548    5/7/2009	Shipped in part
> > 433548    5/9/2009     433548    5/9/2009	Shipped full
> > 433601    5/7/2009     433548    5/7/2009	Shipped in part
> > 433665    5/7/2009     433548    5/7/2009	Shipped in part
> > 519080    5/8/2009     433548    5/8/2009	On hold
> > 519080    5/9/2009     433548    5/9/2009	Shipped full
> > 
0
Utf
2/18/2010 6:44:03 PM
This might come close to what you are looking for:
In call F2 type and copy down till F4:
{=INDEX(E$2:E$8,SMALL(IF(A$2:A$8=A2,ROW(F$2:F$8)-1,9),ROW()-1))}
This is an array formula, and is to be entered with CTRL+SHIFT+ENTER rather 
than with simply ENTER.
The curly brackets {} are not to be typed manually, those are entered by the 
“Excel”, when the formula is entered as an Array formula.
Micky


"HFST04" wrote:

> I need col F to return the status "On Hold, Shipped in Part, Shipped in full" 
> by matching the order # and date in col C and D to col A and B. 
> What I'm looking for is a formula that says if A1 (433548) = C1 (433548) and 
> B1(4/29/2009) = D1 (4/29/2009) then F = On Hold. If A2 (433548) = C2 (433548) 
> and B2(5/7/2009) = D (5/7/2009) then F = Shipped in part.
> The problem i have with my vlookup is it rerutrns the same Status (On Hold 
> etc) for each order number, but I need the status based on order number and 
> date.
> Thank you
> A             B                C            D                E
> 433548    4/29/2009   433548    4/29/2009	On hold
> 433548    5/7/2009     433548    5/7/2009	Shipped in part
> 433548    5/9/2009     433548    5/9/2009	Shipped full
> 
> 
> "מיכאל (מיקי) אבידן" wrote:
> 
> > It is too difficult [for me] to understand what you are after.
> > As there are only a few orders in you example - would you be so kind to 
> > present [typed by hand] the col. F results !?
> > Micky
> > 
> > 
> > 
> > "HFST04" wrote:
> > 
> > > I neeed to merge 16000 rows of data with some duplicate vales. Poor record 
> > > keeping lead to two lists and each was updated independantly if at all. Some 
> > > orders show up one list and not the other so I can't just sort or do a dup 
> > > search. I'm trying to recreate this data and track the status of all orders 
> > > for the past 12 months. When orders were shipped out in parts the same order 
> > > number was used and this has been a nightmare trying to sort the duplicates 
> > > and match the dates.
> > > I need a formula for F that returns the current status based on the order 
> > > number and date. I tried a simple vlookup, but it returns the same status for 
> > > each order #. I need the order status based on number and date. Please HELP.
> > >    A          B                C            D                E               
> > >         F
> > > Order #   Date           Order #   Date	Status                
> > > 433548    4/29/2009   433548    4/29/2009	On hold
> > > 433548    5/7/2009     433548    5/7/2009	Shipped in part
> > > 433548    5/9/2009     433548    5/9/2009	Shipped full
> > > 433601    5/7/2009     433548    5/7/2009	Shipped in part
> > > 433665    5/7/2009     433548    5/7/2009	Shipped in part
> > > 519080    5/8/2009     433548    5/8/2009	On hold
> > > 519080    5/9/2009     433548    5/9/2009	Shipped full
> > > 
0
Utf
2/18/2010 8:04:07 PM
Reply:

Similar Artilces:

Excel Drop Down Boxes #2
Question; does anybody know how to add additional data to a previous drop down box? I have created a form that has several drop down boxes a while ago, and due to some information that has been change I need to input more info in the drop down boxes but for some odd reason I cant remember how I executed these actions!! If it helps I am using Excel 2000. -- CRS ------------------------------------------------------------------------ CRS's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27444 View this thread: http://www.excelforum.com/showthread.php?threadid=46...

Smith's SK2 2-Stone Sharpening Kit
Price:$13.95 Image: http://thediscountlocator.info/image.php?id=B000B5JXU2 Best deal: http://thediscountlocator.info/index.php?id=B000B5JXU2 Deluxe Sharpening Kit, Contains 1 Each 5" X 5/8" Medium Arkansas Stone, 1 Each 4" X 1" Fine Arkansas Stone, 2 OZ Honing Oil. SIMILAR PRODUCTS: 550lb. Type III Paracord:http://thediscountlocator.info/index.php?id=B000S5ODO6 Swedish Firesteel- Army Model:http://thediscountlocator.info/index.php?id=B0013L8D9K Enter The Kettlebell! Strength Secret of The Soviet Supermen:http://thediscountlocator.info/index.php?id=0938045695...

Average formula where blank cells are counted as zeros
I am trying to write an average formula that takes into account the blank cells. I had only cells A2, A4, A6 filled out, but when I do =average(A1:A6), it is not treating the blanks as zeros so the answer is much higher than it should be. Should I do a logic formula within the cells? If you know the range, you could do the division yourself: =sum(a1:a6)/6 But this would include other non-numeric cells in the count of cells (6), too. krwelling wrote: > > I am trying to write an average formula that takes into account the blank > cells. > > I ha...

need a date look here
This is a multi-part message in MIME format --=_NextPart_2rfkindysadvnqw3nerasdf Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Get all the hots girls @ www.pearlymae.com --=_NextPart_2rfkindysadvnqw3nerasdf Content-Type: text/html Content-Transfer-Encoding: quoted-printable <html> <head> <meta name=3D"GENERATOR" content=3D"Microsoft FrontPage 6.0"> <meta name=3D"ProgId" content=3D"FrontPage.Editor.Document"> <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dwindow...

Do we need to install active sync?
Hi all, We have Exchange 2003 Sp2 frontend and backedn servers. I enabled the mobile function in the global seetings. BTW, do we still need to install active sync in the server ot workstaions? Thanks. In news:CD56A612-B0ED-47F0-9428-0B2584DE3AB4@microsoft.com, Sally <Sally@discussions.microsoft.com> typed: > Hi all, > > We have Exchange 2003 Sp2 frontend and backedn servers. I enabled the > mobile function in the global seetings. BTW, do we still need to > install active sync in the server ot workstaions? > > Thanks. You don't install ActiveSync deskt...

Combo Box Value List/Query Criteria
Hi Folks - I have a combo box with a value list of Option1, Option2 and ALL. I pass this combo box info into a query. Here's a sample of the query criteria: IIf([Forms]![frmSwitchboard]![cboCaseType]="all",Is Null,[Forms]![frmSwitchboard]![cboCaseType]) In other words, if the combo box selection is ALL, then display all records, otherwise use selected option. The above criteria does not work. Any suggestions? Thanks. Michael Michael, Try... (IsNull is an operator, not a value) IIf([Forms]![frmSwitchboard]![cboCaseType]="All", Null,[Forms]![frmSwitchboar...

Formula Question (MPS)
i have a formula i use in a spreadsheet... = IF(YEAR(O$1)-YEAR(E3)=105,,YEAR(O$1)-YEAR(E3)) I use it to caculate ages where the current date is in cell O1 and the Person's birthday is shown in cell E3. It doesn't add up right because if the birthday is later in the year than the current day (shown in cell O1. ) It works fine if the birthday is. If the birthday has already happened this year, then the formula works fine. How shoud i adjust it? thanks. Mike instead of adjusting, how about using =DATEDIF(E3,O1,"y") or =DATEDIF(E3,TODAY(),"y") ?? ...

Money 2002 Died #2
Experiencing the same situation. Please inform us of the situation. Thanks! We have the same problem here. Money won't start up. We reinstalled and it ran initially, but now it has the same problem again. Has anyone contacted Microsoft?! >-----Original Message----- >Experiencing the same situation. Please inform us of the >situation. Thanks! >. > Same thing here. This is scary! >-----Original Message----- >We have the same problem here. Money won't start up. We >reinstalled and it ran initially, but now it has the same >problem again. Has a...

Event id: 474 2 days after offline defrag
Last week my customers exchange 2000 information store dismounted and stopped due to it's size exceeding the max 16gigs. I had to run an offline defrag which lowered the databse to below 16 gigs and it mounted properly. That night the was a good backup. Since then there has not been a good backup - basically because of Event id:474 - a chksum error. I an afraid to restart that server or touch it because all of the email is working - all is OK except it cannot be backed up. We are prepared to order another server and run exmerge to migrate the data - is this the best method - or is...

Calculating or Adding Values that are 0
I have three fields that I need to total. When i do run the total nothing appears because one of the fields has a 0 value. The expression is not counting 0 values, how do I work around this? Is the value actually zero or Null? If it is null then use the NZ function like this -- Nz([Field1], 0) + Nz([Field2], 0) + Nz([Field3], 0) -- Build a little, test a little. "blanch2010" wrote: > I have three fields that I need to total. When i do run the total nothing > appears because one of the fields has a 0 value. > > The expression is not ...

Changing Inventory Item Types #2
In GP7.5, is there any way to change inventory item types once they have been used? Can the item be deleted and then recreated at the end of a year? Are there any utility programs that can do this? In particular, I've got some "sales inventory" items that I want to convert to "kits" and others that need to be "services" We are on 8.0, but I don't think it is possible to change the item type - not even with Professional System Tools. We have changed the item name (via tools) to include the word Discontinued. Then we set up a new item to repl...

Formula that checks balances
I have a long list of formulas in column "E". I need to know at a glance whether any value in this column is inequal to zero without having to page down all the way through thousands of rows. Summing the column is no good because there could be offsetting values (e.g. +5 + -5 = 0). I would like a formula I could place in one cell that would indicate if every formula in the column evaluates to zero or not (rounded to 2 decimals). Can anyone help? Thanks! "xp" wrote: > I would like a formula I could place in one cell > that would indicate if e...

IF statement help #2
Hi What im trying to do is, Calculate a Cell x say 0.5. If the result is a negative number, sho 0. If it is a positive number, work it out and show the answer. Jus tried, and cant seem to get it right for some reason. I dont want t have to use another cell for no reaon, as I need to present th worksheet This is what I tried, IF((E22*0.5)>0), (E22*0.5), Thank -- RudeYut ----------------------------------------------------------------------- RudeYute's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3295 View this thread: http://www.excelforum.com/showthr...

Annualiized Return different between M06 and M04 for groups and totals
I did an upgrade twice from M04 to M06 to verify the following. The group and total Annualized Retun are much higher in M06 vs M04. The calculations The annualized returns for indivual holdings are the same but the account totals and portfolio totals annualized returns are different. Can anyone else confirm this? -- NeverOutofTune ...

formula #28
how do i show the formula when i need to print Hi You can use Ctrl and ` (which is to the right of number 1 on your heyboard) or you can use Tools / Options / View / Formulas (if my memory serves me right!) Hope this helps. -- Andy. "martin ekins" <martin_ekins69@hotmail.com> wrote in message news:5BE10AD0-0759-4AED-99A9-02203459FD0F@microsoft.com... > how do i show the formula when i need to print ...

Lookup a range of numbers
Hi, I have a table as follows: (Speed) (Dist) 64k 128k 256k 5 1000 1200 1500 10 1600 1700 1800 15 2000 2100 2200 I'd like to have a formula whereby if I key in a Distance of 4 and a speed of 256k, the active cell will show "1500". I tried to use sumproduct but seems like sumproduct can only be used if the Dist is exactly "5", "10", etc. The only alternative I can think of is to list out ALL the integers for Distance. But, is there an easier way? ...

Need to backup Contacts
I am running Outlook 2000 behind Windows ME. I want to make a backup of Contacts but cannot find the location of the file. When I search on *.pab, it returns "not found". I have over 850 entries in Contacts, so I know the file exists somewhere. Any suggestions on where to find or how to back-up? Thanks. Export your contacts folder to a .pst file. The .pab is old and obsolete and has not been used for a number of releases. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all ...

No more new fonts may be applied to this workbook #2
I have Excel 2003 (11.6113.5703) running on windows XP. I have loaded the latest update from the web. When I try to change the font size of text in the axis or label of a chart, a message shows "No more new fonts may be applied to this workbook." When I do a print preview of the charts, the same message shows up and then the print previews would be displayed. I'd appreciate if you can tell me the fix. Jon Peltier has some information on this in his Charting FAQ article: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=209#jon025 and at his web site (use th...

Display value of cell
Using Excel 2000 In a cell the formula shows instead of the value/result, how do I correct this? I want to see the formula. Reply to kdfoxca@yahoo.com Thanks. Are all formulas cells show the formulas or just one? Maybe you have a space before the = (Excel think it is text now) Or your cell is format as text -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Kenya" <anonymous@discussions.microsoft.com> wrote in message news:050b01c3942b$e28b7240$a301280a@phx.gbl... > Using Excel 2000 > In a cell the formula shows instead of the value/resul...

Show this folder as an e-mail Address Book #2
Hi all, I have just created a new contacts list in our public folders. When I go to all the workstations, the "Show this folder as an e-mail Address Book" is unchecked. To save me going to all workstations and checking this setting, is there some way I can set it to "Show this folder as an e-mail Address Book" automatically? Cheers Marty... ....ms... <martin@adg.com.au> wrote: > Hi all, > > I have just created a new contacts list in our public folders. > When I go to all the workstations, the "Show this folder as an e-mail > Address Book&q...

to extract multiple values from an array
Hi, I am into Image processing industry, for each job we create unique code in excel, we Process 20 jobs in a day, I want to list all the job Code in "summary of the day sheet" that we complete for the particular day . On Dec 30, 4:56=A0am, ratan h <ratha...@nextgenalbums.com> wrote: > Hi, > > I am into Image processing industry, for each job we create unique > code in excel, we Process 20 jobs in a day, I want to list all the job > Code in "summary of the day sheet" that we complete for the particular > day . Not enough info but, assuming you are...

updating #2
My microsoft money mutual fund page does not update anymore. Is there any reason? I did not change anything Thanks Dada ...

I'm sending 2 (duplicate) emails each time I send emai
I'm using Office 2007, Outlook and, each time I send an email.. the receiver gets two copies.... any thoughts? Do you have a virus scanner installed which integrates itself with Outlook? Uninstall this integration part of your virus scanner and try again; you'd still be sufficiently protected by your on-access scanner part of the virus scanner. For more details see; http://www.msoutlook.info/question/20 -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http:/...

Compare two Sheets with Conditional Formatting #2
I'm going insane I'm trying to compare two sheets using Walkenbach's approach but for some reason when I use it...certain values don't show differences. Example: Sheet 1 (Range Name is Grid) has 18000 on Cell A1 Sheet 2 (Range Name is OldGrid) has 18000 on Cell A2 So if I change Cell A1 on Sheet 1 to 1 it should show me change but no...it just does nothing. However if I type 133...it shows change. Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=COUNTIF(OldGrid,A1)=0" Selection.FormatConditions(1).Interior.ColorIndex = 40 Help really appreciate...

Investors Needed!!! #5
INVESTMENT OPPORTUNITY! Fork It Over! a Ground Floor Unique Dimension & Concept In the Children's Entertainment Market... For More information http://www.kidseyeview.us ...