| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need a lookup formula that matches 2 values and returns the 3rd va
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need a lookup formula that matches 2 values and returns the 3r
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need a lookup formula that matches 2 values and returns the 3r
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF formula returns blank cells--only want values to show | Excel Worksheet Functions | |||
| Lookup returns hyperlinked values as normal text | Excel Discussion (Misc queries) | |||
| Lookup which returns multiple values which are additive | Excel Worksheet Functions | |||
| V-lookup and summing values if more than 1 matches criteria | Excel Worksheet Functions | |||
| Multiple matches using LOOKUP | Excel Worksheet Functions | |||
Linear Mode
