Vlookup-Match&offset help

Hello,

I am trying to create a list of value under columns J,K,L where the data will be taken from columns A and B.

Attached the workbook:
First example "bob" should have the value 15, 13 and 45 (highlighted in red) - taking the values from the aaa ("B7"), bbb ("B9"), ccc ("B11") under the name "bob"

Similarly "John" should show 675 in cell "J10", 65 in cell "K10 and 75 in cell "L10".

Any idea how to do this? I tried to work the "Match" and "offset" commands but no luck. This is beyond my powers

You don't have to think too far ahead in terms of offset. Instead, dictate the offsets upfront visually and you'll see how it works together.

Please Login or Register  to view this content.

As long as your ranges are identical in length, their placement is irrelevant.

Explained another way:

Imagine you had names every 3 rows in A1:A48, ID's every 3 rows from B2:B49, and a score every 3 rows from C3:C50.

=SUMPRODUCT((A1:A48=X)*(B2:B49=Y)*(C3:C50))

Each range is from the first occurence, to the last occurence. As long as they are all the same, nothing else matters.

How about you just use VBA instead?

This short subroutine will make quick work of this for you.

Please Login or Register  to view this content.

Expand the I9:I13 range as needed.

If you prefer to do it in terms of OFFSET / MATCH:

Formula:

Please Login or Register  to view this content.

I picked 9 as the row height for the OFFSET because it would fit the longest range of data in column A. If you need something more dynamic that computes that height it will take a little more work.

BTW: are the order of the names in the table always going to be in the same order as the data in column A and will they be unique?

Edit: The more I think about it the row height shouldn't be problematic. The MATCHs for aaa, bbb, ccc...will always return the first occurrence no matter how many of them are in the OFFSET range. Just set the height to the largest number of rows between names you would anticipate.

Works fine for me.