Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I select a value from a table based on 2 conditional refs?
I'm trying to select a value from a table based on 2 variable references.
If been looking at using a VLOOKUP function to do this but because there are 2 dependant references I'm a little stuck. I thought that maybe a VLOOKUP with a MATCH function incorporated might be an option as the entries in each column are not unique. To give you an example of what I am trying to achieve I done the table below. Basically if F1=9 & G2=2 then select C1 (*) A B C D F G 1 9 3 * % 9 2 2 9 2 % & 3 9 1 # * 4 7 3 * % Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I select a value from a table based on 2 conditional refs?
Hi
For your example try: =SUMPRODUCT((A1:A4=F1)*(B1:B4=G2)*(C1:C4)) I'm presuming (by the way) that your example is wrong, as I would have expected it to return %. When using SUMPRODUCT, you can't use full column ranges (A:A) and the ranges used must be the same size. Hope this helps. Andy. "Puzzled Percy" <Puzzled wrote in message ... I'm trying to select a value from a table based on 2 variable references. If been looking at using a VLOOKUP function to do this but because there are 2 dependant references I'm a little stuck. I thought that maybe a VLOOKUP with a MATCH function incorporated might be an option as the entries in each column are not unique. To give you an example of what I am trying to achieve I done the table below. Basically if F1=9 & G2=2 then select C1 (*) A B C D F G 1 9 3 * % 9 2 2 9 2 % & 3 9 1 # * 4 7 3 * % Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I select a value from a table based on 2 conditional re
Thanks for your response Andy. Sorry, my example did have a mistake in it.
My actual data contains dates which I should have mentioned in my original email. I don't think that this function would work because of that. I need to find a way to search for a date which matches with a value (eg: 50) which then okays the further selection of another specified date in the same table. Thanks for your feedback. Any further thoughts would be appreciated. Puzzled Percy "Andy" wrote: Hi For your example try: =SUMPRODUCT((A1:A4=F1)*(B1:B4=G2)*(C1:C4)) I'm presuming (by the way) that your example is wrong, as I would have expected it to return %. When using SUMPRODUCT, you can't use full column ranges (A:A) and the ranges used must be the same size. Hope this helps. Andy. "Puzzled Percy" <Puzzled wrote in message ... I'm trying to select a value from a table based on 2 variable references. If been looking at using a VLOOKUP function to do this but because there are 2 dependant references I'm a little stuck. I thought that maybe a VLOOKUP with a MATCH function incorporated might be an option as the entries in each column are not unique. To give you an example of what I am trying to achieve I done the table below. Basically if F1=9 & G2=2 then select C1 (*) A B C D F G 1 9 3 * % 9 2 2 9 2 % & 3 9 1 # * 4 7 3 * % Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I select a value from a table based on 2 conditional re
Hi
There is no reason why this format of formula will not work with dates - as you want. If you post some more accurate data I'll have a go! Andy. "Puzzled Percy" wrote in message ... Thanks for your response Andy. Sorry, my example did have a mistake in it. My actual data contains dates which I should have mentioned in my original email. I don't think that this function would work because of that. I need to find a way to search for a date which matches with a value (eg: 50) which then okays the further selection of another specified date in the same table. Thanks for your feedback. Any further thoughts would be appreciated. Puzzled Percy "Andy" wrote: Hi For your example try: =SUMPRODUCT((A1:A4=F1)*(B1:B4=G2)*(C1:C4)) I'm presuming (by the way) that your example is wrong, as I would have expected it to return %. When using SUMPRODUCT, you can't use full column ranges (A:A) and the ranges used must be the same size. Hope this helps. Andy. "Puzzled Percy" <Puzzled wrote in message ... I'm trying to select a value from a table based on 2 variable references. If been looking at using a VLOOKUP function to do this but because there are 2 dependant references I'm a little stuck. I thought that maybe a VLOOKUP with a MATCH function incorporated might be an option as the entries in each column are not unique. To give you an example of what I am trying to achieve I done the table below. Basically if F1=9 & G2=2 then select C1 (*) A B C D F G 1 9 3 * % 9 2 2 9 2 % & 3 9 1 # * 4 7 3 * % Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I select a value from a table based on 2 conditional refs?
=INDEX(C1:C4,MATCH(1,(A1:A4=F1)*(B1:B4=G1),0))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Puzzled Percy" <Puzzled wrote in message ... I'm trying to select a value from a table based on 2 variable references. If been looking at using a VLOOKUP function to do this but because there are 2 dependant references I'm a little stuck. I thought that maybe a VLOOKUP with a MATCH function incorporated might be an option as the entries in each column are not unique. To give you an example of what I am trying to achieve I done the table below. Basically if F1=9 & G2=2 then select C1 (*) A B C D F G 1 9 3 * % 9 2 2 9 2 % & 3 9 1 # * 4 7 3 * % Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I select a value from a table based on 2 conditional re
Hi Andy,
I should have tried your fomula first before doubting you! (Won't do that again) It works great for what I want it to do given the way that Excel works out dates. You are fantastic :) Regards, Puzzled Percy "Andy" wrote: Hi There is no reason why this format of formula will not work with dates - as you want. If you post some more accurate data I'll have a go! Andy. "Puzzled Percy" wrote in message ... Thanks for your response Andy. Sorry, my example did have a mistake in it. My actual data contains dates which I should have mentioned in my original email. I don't think that this function would work because of that. I need to find a way to search for a date which matches with a value (eg: 50) which then okays the further selection of another specified date in the same table. Thanks for your feedback. Any further thoughts would be appreciated. Puzzled Percy "Andy" wrote: Hi For your example try: =SUMPRODUCT((A1:A4=F1)*(B1:B4=G2)*(C1:C4)) I'm presuming (by the way) that your example is wrong, as I would have expected it to return %. When using SUMPRODUCT, you can't use full column ranges (A:A) and the ranges used must be the same size. Hope this helps. Andy. "Puzzled Percy" <Puzzled wrote in message ... I'm trying to select a value from a table based on 2 variable references. If been looking at using a VLOOKUP function to do this but because there are 2 dependant references I'm a little stuck. I thought that maybe a VLOOKUP with a MATCH function incorporated might be an option as the entries in each column are not unique. To give you an example of what I am trying to achieve I done the table below. Basically if F1=9 & G2=2 then select C1 (*) A B C D F G 1 9 3 * % 9 2 2 9 2 % & 3 9 1 # * 4 7 3 * % Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I select a value from a table based on 2 conditional re
Thanks Bob,
Your formula works great too! This was more what I had in mind when I first posted as I will be able to use text with it in a different scenario. You are FANTASTIC :) Regards, Puzzled Percy "Bob Phillips" wrote: =INDEX(C1:C4,MATCH(1,(A1:A4=F1)*(B1:B4=G1),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Puzzled Percy" <Puzzled wrote in message ... I'm trying to select a value from a table based on 2 variable references. If been looking at using a VLOOKUP function to do this but because there are 2 dependant references I'm a little stuck. I thought that maybe a VLOOKUP with a MATCH function incorporated might be an option as the entries in each column are not unique. To give you an example of what I am trying to achieve I done the table below. Basically if F1=9 & G2=2 then select C1 (*) A B C D F G 1 9 3 * % 9 2 2 9 2 % & 3 9 1 # * 4 7 3 * % Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formating cells i Excel based on other cells values | Excel Worksheet Functions | |||
Filter based on Pivot table | Excel Worksheet Functions | |||
Keep conditional format when "show pages" from Pivot table | Excel Discussion (Misc queries) | |||
can not select conditional formatting | Excel Worksheet Functions | |||
How to create a calculated field formula based on Pivot Table resu | Excel Discussion (Misc queries) |