Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Randomly choosing | Excel Worksheet Functions | |||
Choosing a date | Excel Worksheet Functions | |||
Dropdown Selections in Excel - creating and choosing | Excel Worksheet Functions |