VLOOKUP on 2 sets of criteria

[Guest]

My data set looks something like this.

Test Name Level Spec
Test 1 8 -32
Test 1 9 -32
Test 1 10 -32
Test 1 11 -34
Test 1 12 -34
Test 2 8 -28
Test 2 9 -29
Test 2 10 -31
Test 2 11 -32
Test 2 12 -32

I would like to be able to do a VLOOKUP on Spec, based on a combination of
TestName AND Level

Is this possible?

Thanks
sandy

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(A2:A1000="Test 1"),--(B1:B1000=10),C1:C1000)

 

[Guest]

=Index(C:C,Match("Test 110",$A$1:$A$100&$B$1:$B$100,0),1)

entered with Ctrl+shift+Enter rather than just enter since this is an array
formula.

 

[Guest]

OK - i think I get this logic - thanks!

I have one question:
"Test 110" - this is a concatenation of the values [Test Name = test 1 and
level = 10] in the two fields that I want to look up on, correct?

However, since i need to look up a different value for thousands of records,
I want to create a formula to concatenate the fields and then use a cell
reference instead of specific text.

=INDEX(C:C,MATCH(G2,$A$1:$A$100&$B$1:$B$100,0),1)

Where G2 hold the concatenated value for that record.

This gave me a "VALUE" error... what am i missing here?

Thanks!

 

[Guest]

Pls ignore this last question - I have it working with a cell reference.

Thanks!
sandy

OK - i think I get this logic - thanks!

I have one question:
"Test 110" - this is a concatenation of the values [Test Name = test 1 and
level = 10] in the two fields that I want to look up on, correct?

However, since i need to look up a different value for thousands of records,
I want to create a formula to concatenate the fields and then use a cell
reference instead of specific text.

=INDEX(C:C,MATCH(G2,$A$1:$A$100&$B$1:$B$100,0),1)

Where G2 hold the concatenated value for that record.

This gave me a "VALUE" error... what am i missing here?

Thanks!

=Index(C:C,Match("Test 110",$A$1:$A$100&$B$1:$B$100,0),1)

entered with Ctrl+shift+Enter rather than just enter since this is an array
formula.