Lost on how to handle this. Cross match/reference? (SUMIF/LOOKUP/INDEX/MATCH)

[jaunis]

HI everyone, so I've trying to figure this out for the last 2 hours. I'm not an Excel guru but I've tried googling and searching in this forum as much as possible and from all the options I tried none worked.

I have the following tables / data:

Table named BLUE comes from a survey where agents self-assess their knowledge of a specific skill. Values there are from 1 to 6.

Table named RED is manually modified and contains the weighted skills required for each product. I was originally using blank cells here for skills not needed in a particular product (and zeroes as the lowest value in the BLUE table) but I can change the source parameters and I thought that not having blank cells and zeroes will make the formula easier. (So forgive the little mismatch between my explanation and the image)

Table GREEN should: find the skills for a particular product and multiply them for the responses that we got from an agent. Again, originally skills were not weighted so 1/blank was easier in my head with SUMIF or COUNTIF, but then I had to weight those values and then the mess came.

I have gone trough every similar tutorial I found without success. I'm not looking for an easy answer but more of an explanation on how to address this scenario. Easy answer will be great, of course, but the explanation can allow me learn how to do it and why I couldn't solve it myself.

I also know that I can do this manually, but I would like a formula which is the same for all cells in the GREEN table, looking up values in RED and BLUE and multiplying for each case.

Last table (YELLOW) is just the finished thing were each score is weighted against the product maximum score.

Ideas on how to create the formula for the GREEN table? I added names to the tables because it's friendlier to write formulas using named ranges/arrays.

Thank you a lot,
J

 

[jaunis]

So I get till this point:

=(SUMPRODUCT(RED[Product1]*BLUE[Agent1])/(SUM(RED[Product1])*6))

But I don't know how to make it in a way that I can drag the formula and it will calculate values for all cells.

 

[circledchicken]

Hi jaunis and welcome to the forum,

Maybe try something like this in cell K4 and copy across and down the GREEN table:

=SUMPRODUCT(
    INDEX(BLUE, 0, MATCH($J4, BLUE[#Headers], 0)),
    INDEX(RED, 0, MATCH(K$3, RED[#Headers], 0)))

Note:
- The SUMPRODUCT function multiplies corresponding components in the arrays provided, and returns the sum of those products. For more detail see for example:
https://support.office.com/en-ca/article/SUMPRODUCT-function-57a7bfa7-f74d-4ead-8c93-57f759c8f616
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
- The INDEX / MATCH functions return the required column arrays from the BLUE and RED tables to use as arguments in the SUMPRODUCT function. For more on INDEX / MATCH see for example:
http://www.contextures.com/xlFunctions03.html
Excel Formulas | Excel Hero Blog

In cell K18 and copy across and down the YELLOW table:

=INDEX(
    GREEN,
    MATCH($J18, $J$4:$J$6, 0),
    MATCH(K$17, GREEN[#Headers], 0))
  /
  SUMPRODUCT(6 * INDEX(RED, 0, MATCH(K$17, RED[#Headers], 0)))

Note:
- The INDEX / MATCH bit gets the relevant value from the GREEN table.
- The SUMPRODUCT bit calculates the maximum possible value for each product. Alternatively you could calculate this outside the table and just reference that calculation e.g. cells M11 and M12 in your picture.

Also please note:
- With this setup blanks are ok in the BLUE and RED tables (they will be treated like zeros by the SUMPRODUCT function).
- I'm assuming some of the values in your GREEN and YELLOW tables as currently shown in the picture are incorrect (e.g. cell L6 should be 2 rather than 15).

 

[jaunis]

Thank you so much! @circledchicken. Amazing explanation. Just a last question before I try something: Will this formula work if data in BLUE, RED, or both is transposed? (I later realized that BLUE adds a ROW for every response, not a column.

I could do it manually when getting the source data but I was wondering if I could transpose BLUE (GREEN I can't), modify the formula, and it would still work.

I believe that for this to work the shared information needs to be in the first column but I thought it would be good to ask.

 

[circledchicken]

Hi jaunis,

You're welcome.

I'm not sure exactly what you mean - perhaps post a small sample of the transposed data and expected results.

Please use one of the following methods to post data rather than a picture:

- Borders-Copy-Paste: http://www.mrexcel.com/forum/2198045-post2.html
- MrExcel HTML Maker: https://skydrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189
- Excel jeanie: Excel Jeanie Html

 

[jaunis]

For BLUE, instead of what the image shows above (notice that column/rows are switched):

[TABLE="width: 397"]
<tbody>[TR]
[TD]Column1[/TD]
[TD]Skill1[/TD]
[TD]Skill2[/TD]
[TD]Skill3[/TD]
[TD]Skill4[/TD]
[TD]Skill5[/TD]
[/TR]
[TR]
[TD]Agent1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Agent2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Agent3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

A transposing example taken from Office website:

 

[circledchicken]

Hi jaunis,

The same formula wouldn't work if you transpose the BLUE table, but you can amend the GREEN table formula slightly to something like this:

=SUMPRODUCT(
    TRANSPOSE(INDEX($B$4:$F$6, MATCH($L4, $A$4:$A$6, 0), 0)),
    INDEX(RED, 0, MATCH(M$3, RED[#Headers], 0)))

Note:
- You need to press CTRL-SHIFT-ENTER rather than just ENTER as this is an array formula
- Adjust the ranges as appropriate based on your transposed table
- In this setup I've assumed that the BLUE table now covers the range A3:F6, and the other tables have also shifted accordingly as a result)

A simpler solution might be if you can transpose the RED table as well, and then use the following:

In the GREEN table:

=SUMPRODUCT(
    INDEX($B$4:$F$6, MATCH($O4, $A$4:$A$6, 0), 0),
    INDEX($I$4:$M$5, MATCH(P$3, $H$4:$H$5, 0), 0))

In the YELLOW table:

=INDEX(
    GREEN,
    MATCH($O18, $O$4:$O$6, 0),
    MATCH(P$17, GREEN[#Headers], 0))
  /
  SUMPRODUCT(6 * INDEX($I$4:$M$5, MATCH(P$3, $H$4:$H$5, 0), 0))

Adjust the table ranges as required.
My assumed table ranges for this second setup were as follows (including the column headers):
- BLUE, A3:F6
- RED, H3:M5
- GREEN, O3:Q6
- YELLOW, O17:Q20