Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup/Match/Not sure
Could you tell me the best way to accomplish my goal...
I have a master spreadsheet that contains customer name and customer ID. I have data spreadsheet that contains customer name, customer ID, Call type (Inbound or outbound), reason code, date. I need the master spreadsheet to reference the data spreadsheet and look for a match of customer ID, and return call type and reason code. If there is more than one match, to look at the date and return the most recent call type, reason code and date. If there is no match, then leave blank. Example: Master A B 1 Mickey 1234 Data A B C D E 1 Mickey 1234 Outbound LM 9/1/2009 2 Mickey 1234 Inbound Comp 9/2/2009 Updated Master A B C D E 1 Mickey 1234 Inbound Comp 9/2/2009 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup/Match/Not sure
If you have follow-up questions, I recommend using the Excel Worksheet
Functions newsgroup, as this group is focused on VBA programming. However, to answer your question: enter the following as an array formula (e.g. Ctrl-Shift-Enter). Assumes your data sheet is Sheet2, with 20 rows of data. (aircode) =MAX(IF(Sheet2!B1:B20=B22,Row(Sheet2!E1:E20),0)) will give you your target row number *if* your entries are always in chronological order. You can then use index, indirect, or various other formulas to return the values of each cell. If your entries are /not/ in chronological order, that presents more of a challenge. Also, you didn't mention if the same customer might have more than one entry on the same date; if they can but the data is still in chronological order, the above formula should still work. However, if they can have multiple entries on the same date and the data is /not/ in chronological order, then maybe someone in the worksheet functions can provide more detailed help. HTH, Keith "Whitney" wrote: Could you tell me the best way to accomplish my goal... I have a master spreadsheet that contains customer name and customer ID. I have data spreadsheet that contains customer name, customer ID, Call type (Inbound or outbound), reason code, date. I need the master spreadsheet to reference the data spreadsheet and look for a match of customer ID, and return call type and reason code. If there is more than one match, to look at the date and return the most recent call type, reason code and date. If there is no match, then leave blank. Example: Master A B 1 Mickey 1234 Data A B C D E 1 Mickey 1234 Outbound LM 9/1/2009 2 Mickey 1234 Inbound Comp 9/2/2009 Updated Master A B C D E 1 Mickey 1234 Inbound Comp 9/2/2009 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I lookup when match has more than one value? | Excel Worksheet Functions | |||
Lookup Formula: Return 1st match, then 2nd match, then 3rd match | Excel Discussion (Misc queries) | |||
Lookup, Match, something like this | Excel Worksheet Functions | |||
Match or Lookup | Excel Discussion (Misc queries) | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions |