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

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

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

Lookup in table with two equal references
 

Great! Simple and effective. Thanks a lot.

Lookup in table with two equal references
 

That's what I like most. KISS. Keep It Stupid Simple. e=mc2.
--
topola, www.seeit.ehost.pl