Lookup Question

[andrewc]

Hi,

I have sample data in columns A and B of the attached file.

In cells F2 and G2 I wish to create formulae to extract from columns A and B the rank of the product in cell E2 and also the number in its group.

The formula in F2 can be simply a VLOOKUP function as there is a single instance of each product name in column A.

The difficulty arises when trying to extract the “number in group” value related to a particular product; a VLOOKUP won’t work as it will return the value associated with the first instance that it finds in column A, 12, rather than 15, in my example.

The data in columns A and B is set up so that the “number in group” associated with a particular product is the next instance below it in column A. So, the “number in group” for BBB is 12, for DDD is 15 and for FFF is 21.

Can someone please suggest a formula that will extract the correct value to cell G2.

Thanks!