Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP on 2 sets of criteria
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP on 2 sets of criteria
One way:
=SUMPRODUCT(--(A2:A1000="Test 1"),--(B1:B1000=10),C1:C1000) In article , Sandy wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP on 2 sets of criteria
=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. -- Regards, Tom Ogilvy "Sandy" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP on 2 sets of criteria
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! "Tom Ogilvy" wrote: =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. -- Regards, Tom Ogilvy "Sandy" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP on 2 sets of criteria
Pls ignore this last question - I have it working with a cell reference.
Thanks! sandy "Sandy" wrote: 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! "Tom Ogilvy" wrote: =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. -- Regards, Tom Ogilvy "Sandy" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup with 4 sets of criteria to match? | Excel Worksheet Functions | |||
IF FUCTION WITH 2 SETS OF CRITERIA | Excel Worksheet Functions | |||
Vlookup for 2 sets of Criteria (or do I need to use something else | Excel Discussion (Misc queries) | |||
vlookup with 2 sets of criteria | Excel Discussion (Misc queries) | |||
SUMIF with two sets of criteria | Excel Discussion (Misc queries) |