Choosing a value from a row with more than one condition
 

Hi,
I have a table with some duplicate values in various fields. I need to
extract a value from a certain row that answers to two different creterias:

Name Skill Score Active
1. John Craft 2 no
2. John Track 3 no
3. Dana Craft 7 yes
4. Dana Track 2 yes

In the example above I am trying to get the Score value where Skill =
"Craft" and Active = "yes"
VLOOKUP is obviously not good enough as it gets me the first Craft it
encounters without considering a second creteria.
How do I select from a table with two conditions at hand?

Suppose your information is in cells A1 to E5.
In cell G2 enter =C2&E2 and copy to the other cells in the column.
The formula is:
=VLOOKUP(SUMPRODUCT(--(G2:G5="Craftyes"),A2:A5),$A$2:$E$5,4)

"Yossi" wrote:

Hi,
I have a table with some duplicate values in various fields. I need to
extract a value from a certain row that answers to two different creterias:

Name Skill Score Active
1. John Craft 2 no
2. John Track 3 no
3. Dana Craft 7 yes
4. Dana Track 2 yes

In the example above I am trying to get the Score value where Skill =
"Craft" and Active = "yes"
VLOOKUP is obviously not good enough as it gets me the first Craft it
encounters without considering a second creteria.
How do I select from a table with two conditions at hand?

Thank you , Martin
Can you please explain what this expression means?
--(G2:G5="Craftyes")

"Martin P" wrote:

Suppose your information is in cells A1 to E5.
In cell G2 enter =C2&E2 and copy to the other cells in the column.
The formula is:
=VLOOKUP(SUMPRODUCT(--(G2:G5="Craftyes"),A2:A5),$A$2:$E$5,4)

"Yossi" wrote:

Hi,
I have a table with some duplicate values in various fields. I need to
extract a value from a certain row that answers to two different creterias:

Name Skill Score Active
1. John Craft 2 no
2. John Track 3 no
3. Dana Craft 7 yes
4. Dana Track 2 yes

In the example above I am trying to get the Score value where Skill =
"Craft" and Active = "yes"
VLOOKUP is obviously not good enough as it gets me the first Craft it
encounters without considering a second creteria.
How do I select from a table with two conditions at hand?

It tells you whether or not a cell in the range G2:G5 has as its result
="Craft"&"yes". 1 for it does and 0 for it does not. You can gain insight by
looking at the sumproduct dialogue box. Put the cursor somewhere in
SUMPRODUCT and click on the function symbol.
You can also get the result you asked for in your first post without
entering an additional column:
=VLOOKUP(SUMPRODUCT(--(C2:C5&E2:E5="craftyes"),$A$2:$A$5),$A$2:$E$5,4)

"Yossi" wrote:

Thank you , Martin
Can you please explain what this expression means?
--(G2:G5="Craftyes")

"Martin P" wrote:

Suppose your information is in cells A1 to E5.
In cell G2 enter =C2&E2 and copy to the other cells in the column.
The formula is:
=VLOOKUP(SUMPRODUCT(--(G2:G5="Craftyes"),A2:A5),$A$2:$E$5,4)

"Yossi" wrote:

Hi,
I have a table with some duplicate values in various fields. I need to
extract a value from a certain row that answers to two different creterias:

Name Skill Score Active
1. John Craft 2 no
2. John Track 3 no
3. Dana Craft 7 yes
4. Dana Track 2 yes

In the example above I am trying to get the Score value where Skill =
"Craft" and Active = "yes"
VLOOKUP is obviously not good enough as it gets me the first Craft it
encounters without considering a second creteria.
How do I select from a table with two conditions at hand?

thanks.
that should come in handy :-)

"Martin P" wrote:

It tells you whether or not a cell in the range G2:G5 has as its result
="Craft"&"yes". 1 for it does and 0 for it does not. You can gain insight by
looking at the sumproduct dialogue box. Put the cursor somewhere in
SUMPRODUCT and click on the function symbol.
You can also get the result you asked for in your first post without
entering an additional column:
=VLOOKUP(SUMPRODUCT(--(C2:C5&E2:E5="craftyes"),$A$2:$A$5),$A$2:$E$5,4)

"Yossi" wrote:

Thank you , Martin
Can you please explain what this expression means?
--(G2:G5="Craftyes")

"Martin P" wrote:

Suppose your information is in cells A1 to E5.
In cell G2 enter =C2&E2 and copy to the other cells in the column.
The formula is:
=VLOOKUP(SUMPRODUCT(--(G2:G5="Craftyes"),A2:A5),$A$2:$E$5,4)

"Yossi" wrote:

Hi,
I have a table with some duplicate values in various fields. I need to
extract a value from a certain row that answers to two different creterias:

Name Skill Score Active
1. John Craft 2 no
2. John Track 3 no
3. Dana Craft 7 yes
4. Dana Track 2 yes

In the example above I am trying to get the Score value where Skill =
"Craft" and Active = "yes"
VLOOKUP is obviously not good enough as it gets me the first Craft it
encounters without considering a second creteria.
How do I select from a table with two conditions at hand?