Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup in table with two equal references
Hello:
I have a table that is monthly retrieved from an accounting application. I post a table as an example: Ref Product Seller Profit 1 A 1 4 2 B 1 7 2 B 2 9 3 C 1 3 In another table I must have the following results: Ref Profit 1 4 2 7 2 9 3 3 There is no way I can change the reference numbers automatically. As you can see, I can't use vlookup as it stops in the first cell that matches the "2" Ref, retrieving always the same value 7. The question is simple: how do I retrieve the "9" in the second "2" reference ? Thanks M |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup in table with two equal references
The job in your example can be done by recording a simple macro that copies
column A (Ref) in table 1 to column A in table 2 and column D (Profit) in table 1 to column B in table 2. Or is there something more complicated to be done? Regards, Stefi €žMaxtrix€ť ezt Ă*rta: Hello: I have a table that is monthly retrieved from an accounting application. I post a table as an example: Ref Product Seller Profit 1 A 1 4 2 B 1 7 2 B 2 9 3 C 1 3 In another table I must have the following results: Ref Profit 1 4 2 7 2 9 3 3 There is no way I can change the reference numbers automatically. As you can see, I can't use vlookup as it stops in the first cell that matches the "2" Ref, retrieving always the same value 7. The question is simple: how do I retrieve the "9" in the second "2" reference ? Thanks M |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup in table with two equal references
Hi, your table looks like the reference number is corresponding to the
Product. What you are actually looking for is a Profit = function (Product, Seller). Thus you are missing a Seller as the second argument. I would use another column A to concatenate Product and Seller A2=B2&"_"&C2 where B2 is Product, C2 is Seller Now you can vlookup new column A values. -- HTH Topola, http://vba.blog.onet.pl |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup in table with two equal references
Great! Simple and effective. Thanks a lot.
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup in table with two equal references
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Help with lookup values in Table | Excel Discussion (Misc queries) | |||
Multiple table lookup | Excel Discussion (Misc queries) | |||
can lookup a number to a 10x10 table? | Excel Worksheet Functions | |||
auto fill data into a cell from a lookup table | Excel Discussion (Misc queries) | |||
Table Lookup | Excel Worksheet Functions |