Should I use VLOOKUP or INDEX and MATCH (array) to calculate the data I need? (Part 1?)

[HeyInKy]

I have a worksheet (Sheet1) with categories in the rows of column B; to be exact, it's B37:B71 (Row 37 is the header row, and Row 71 is the totals row). Some of the categories are identical, for example, 6 of the rows my be labeled Blue, 2 rows may be labeled Red, while only 1 row may be labeled Magenta, etc., but every row is labeled something. My corresponding 4 columns of numeric data for these rows lay in F38:J70 (columns B-E are used for extraneous data in rows 1-36, by the way). Each column has a label: Group 1, Group 2, Group 3 and Group 4.

I want a formula that can find the values for ALL the rows with the label "Blue" in "Group 3" - and give me the sum. Also, the formula will be on Sheet2. Any help is much appreciated! If this works - I may have a part 2....

HeyInKy

[Olly]

Please attach a workbook.

It sounds like a Sumif combined with an Offset would do the trick, but it's much easier to show you within your workbook structure.

[HeyInKy]

Thanks... technically, the SUMIF formula works (no OFFSET needed) to capture just the row category I need... but, and this is my fault for not putting it in the original question, if I have to capture another piece of data, a different category or a different (column) group, then that gets cumbersome... so I tried to create an INDEX/MATCH lookup where I could name what row and column I was wanting - but it's not working correctly. I used data validation in K10 and K12 - but it because a few categories, sub-categories actually, are duplicate, it doesn't always pull the correct data.

All the formulas are in column K. Again, on the final product, I'll have the formulas on a different worksheet - for convenience, here I put the formulas on the same worksheet and deleted the extraneous data.

[Olly]

In cell K14:

Please Login or Register  to view this content.

[HeyInKy]

Awesome. Big Thanks! It'll take me some time to deconstruct and understand all that's going on there - but it works.

Follow-up question - my data validation in K10 is the entire category range (probably should've used named ranges, but the data is uploaded from an SQL database and is pre-set)... and your formula works the way I want it to in that anytime "Orange" is selected in K10, it pulls ALL the oranges. So can I narrow my long list in the data validation to remove the repeat categories?