Return matched value 2 criteria

  • Thread starter Thread starter Diddy
  • Start date Start date
D

Diddy

Hi,

I'm tying myself up in knots trying to get this to work.

I have employee test data in a single sheet.

K is a helper column with a unique number identifying the employee. M is the
Test and p is the result (1-5)

I would like to be able to set up a formula so that data stored in P can be
matched with employee in rows.

eg
Col A Col M Col P
Robert test 1 result 5
Robert test 2 result 4
Robert test 3 result 5

Emp test1 test 2 test 3
Change to Robert 5 4 5

Sorry I know not very clear
 
Diddy,

Select your data table, then use Data / Pivot Table.... and click Finish.

Then drag the heading from column A to the Row area, column M to the Column Area, and Result to the
data area.

HTH,
Bernie
MS Excel MVP
 
Hi,

A pivot table is a nice solution or if you want to use a formula

=SUMPRODUCT(--($M$2:$M$100=E$1),--($K$2:$K$100=$D2),$P$2:$P$100)

Where the test names are listed in E1:G1 for example, the persons identifier
is in D2 the formula above would go into E2 and be copied down and to the
right as far as necessary.

If this helps, please click the Yes button

Cheers,
Shane Devenshire
 
Hi Shane,

The formula worked brilliantly. Would it work for text formats, I'm guessing
not - but haven't seen sumproduct used like this before.

Thank you so much :-)
 
Hi Bernie,

Very neat :-) I've never used pivot tables before and not as scary as I
thought

I will probably use Shane's formula for this one but will definitely be
using pivot tables again.

Cheers
Diddy
 
Back
Top