| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup 2 values to return 1
Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to
lookup 2 different dates in a table and if both of those dates appear on one sheet, I need the associated value to be returned. If this is possible, how would I do this? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup 2 values to return 1
Hi,
An example will help but assuming that you want to extract a value from column C if the dates in column D and E match your dates entered in cells A1 and B1 and put the value in A2 use in A2 =sumproduct(--(A1=$D$1:$D$1000),--(B1=$E$1:$E$1000),$C$1:$C$1000) "Sarah" wrote: Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to lookup 2 different dates in a table and if both of those dates appear on one sheet, I need the associated value to be returned. If this is possible, how would I do this? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup 2 values to return 1
Hi,
What I am looking at cell A1 and B1 from Sheet1 and looking up those two dates in a table array on Sheet2. If both of those dates are found in lets say cell B2 and C2 then I want the value in that table on sheet2, cell D2 to be returned on Sheet1. Does this help at all? "Eduardo" wrote: Hi, An example will help but assuming that you want to extract a value from column C if the dates in column D and E match your dates entered in cells A1 and B1 and put the value in A2 use in A2 =sumproduct(--(A1=$D$1:$D$1000),--(B1=$E$1:$E$1000),$C$1:$C$1000) "Sarah" wrote: Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to lookup 2 different dates in a table and if both of those dates appear on one sheet, I need the associated value to be returned. If this is possible, how would I do this? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup 2 values to return 1
Hi Sarah,
try =sumproduct(--(A1=sheet2!$B$1:$B$1000),--(B1=sheet2!C$1:$C$1000),sheet2!$D$1:$D$1000) formula will look what you have in cell a1 and b1 through columns B and C in sheet2 and when match is found will get total from column D Hope this helps "Sarah" wrote: Hi, What I am looking at cell A1 and B1 from Sheet1 and looking up those two dates in a table array on Sheet2. If both of those dates are found in lets say cell B2 and C2 then I want the value in that table on sheet2, cell D2 to be returned on Sheet1. Does this help at all? "Eduardo" wrote: Hi, An example will help but assuming that you want to extract a value from column C if the dates in column D and E match your dates entered in cells A1 and B1 and put the value in A2 use in A2 =sumproduct(--(A1=$D$1:$D$1000),--(B1=$E$1:$E$1000),$C$1:$C$1000) "Sarah" wrote: Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to lookup 2 different dates in a table and if both of those dates appear on one sheet, I need the associated value to be returned. If this is possible, how would I do this? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup 2 values to return 1
Yes. Modify Eduardo's formula to fit your ranges and layout. Something like this:
=SUMPRODUCT(--(A1=Sheet2!$B$1:$B$1000),--(B1=Sheet2!$C$1:$C$1000),Sheet2!$D$1:$D$1000) Sarah wrote: Hi, What I am looking at cell A1 and B1 from Sheet1 and looking up those two dates in a table array on Sheet2. If both of those dates are found in lets say cell B2 and C2 then I want the value in that table on sheet2, cell D2 to be returned on Sheet1. Does this help at all? "Eduardo" wrote: Hi, An example will help but assuming that you want to extract a value from column C if the dates in column D and E match your dates entered in cells A1 and B1 and put the value in A2 use in A2 =sumproduct(--(A1=$D$1:$D$1000),--(B1=$E$1:$E$1000),$C$1:$C$1000) "Sarah" wrote: Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to lookup 2 different dates in a table and if both of those dates appear on one sheet, I need the associated value to be returned. If this is possible, how would I do this? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup 2 values to return 1
That didnt work. The return value is actually a text so a sum wouldnt work.
Thanks though! "Eduardo" wrote: Hi Sarah, try =sumproduct(--(A1=sheet2!$B$1:$B$1000),--(B1=sheet2!C$1:$C$1000),sheet2!$D$1:$D$1000) formula will look what you have in cell a1 and b1 through columns B and C in sheet2 and when match is found will get total from column D Hope this helps "Sarah" wrote: Hi, What I am looking at cell A1 and B1 from Sheet1 and looking up those two dates in a table array on Sheet2. If both of those dates are found in lets say cell B2 and C2 then I want the value in that table on sheet2, cell D2 to be returned on Sheet1. Does this help at all? "Eduardo" wrote: Hi, An example will help but assuming that you want to extract a value from column C if the dates in column D and E match your dates entered in cells A1 and B1 and put the value in A2 use in A2 =sumproduct(--(A1=$D$1:$D$1000),--(B1=$E$1:$E$1000),$C$1:$C$1000) "Sarah" wrote: Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to lookup 2 different dates in a table and if both of those dates appear on one sheet, I need the associated value to be returned. If this is possible, how would I do this? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup 2 values to return 1
The more information you provide up front, the easier it is to provide a working
solution. Try this array formula (commit with CTRL+SHIFT+ENTER): =INDEX(Sheet2!$B$2:$D$1000,MATCH(A1&B1, Sheet2!$B$2:$B$1000&Sheet2!$C$2:$C$1000,0),3) Sarah wrote: That didnt work. The return value is actually a text so a sum wouldnt work. Thanks though! "Eduardo" wrote: Hi Sarah, try =sumproduct(--(A1=sheet2!$B$1:$B$1000),--(B1=sheet2!C$1:$C$1000),sheet2!$D$1:$D$1000) formula will look what you have in cell a1 and b1 through columns B and C in sheet2 and when match is found will get total from column D Hope this helps "Sarah" wrote: Hi, What I am looking at cell A1 and B1 from Sheet1 and looking up those two dates in a table array on Sheet2. If both of those dates are found in lets say cell B2 and C2 then I want the value in that table on sheet2, cell D2 to be returned on Sheet1. Does this help at all? "Eduardo" wrote: Hi, An example will help but assuming that you want to extract a value from column C if the dates in column D and E match your dates entered in cells A1 and B1 and put the value in A2 use in A2 =sumproduct(--(A1=$D$1:$D$1000),--(B1=$E$1:$E$1000),$C$1:$C$1000) "Sarah" wrote: Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to lookup 2 different dates in a table and if both of those dates appear on one sheet, I need the associated value to be returned. If this is possible, how would I do this? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup 2 values to return 1
Actually, the column selection in the INDEX() is not necessary if we define the
array as just column D: =INDEX(Sheet2!$D$2:$D$1000,MATCH(A1&B1, Sheet2!$B$2:$B$1000&Sheet2!$C$2:$C$1000,0)) Glenn wrote: The more information you provide up front, the easier it is to provide a working solution. Try this array formula (commit with CTRL+SHIFT+ENTER): =INDEX(Sheet2!$B$2:$D$1000,MATCH(A1&B1, Sheet2!$B$2:$B$1000&Sheet2!$C$2:$C$1000,0),3) Sarah wrote: That didnt work. The return value is actually a text so a sum wouldnt work. Thanks though! "Eduardo" wrote: Hi Sarah, try =sumproduct(--(A1=sheet2!$B$1:$B$1000),--(B1=sheet2!C$1:$C$1000),sheet2!$D$1:$D$1000) formula will look what you have in cell a1 and b1 through columns B and C in sheet2 and when match is found will get total from column D Hope this helps "Sarah" wrote: Hi, What I am looking at cell A1 and B1 from Sheet1 and looking up those two dates in a table array on Sheet2. If both of those dates are found in lets say cell B2 and C2 then I want the value in that table on sheet2, cell D2 to be returned on Sheet1. Does this help at all? "Eduardo" wrote: Hi, An example will help but assuming that you want to extract a value from column C if the dates in column D and E match your dates entered in cells A1 and B1 and put the value in A2 use in A2 =sumproduct(--(A1=$D$1:$D$1000),--(B1=$E$1:$E$1000),$C$1:$C$1000) "Sarah" wrote: Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to lookup 2 different dates in a table and if both of those dates appear on one sheet, I need the associated value to be returned. If this is possible, how would I do this? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup 2 values to return 1
Hi,
try =if(sumproduct(--(A1=sheet2!$B$1:$B$1000),--(B1=sheet2!C$1:$C$1000))0,sheet2!$D$1:$D$1000) "Sarah" wrote: That didnt work. The return value is actually a text so a sum wouldnt work. Thanks though! "Eduardo" wrote: Hi Sarah, try =sumproduct(--(A1=sheet2!$B$1:$B$1000),--(B1=sheet2!C$1:$C$1000),sheet2!$D$1:$D$1000) formula will look what you have in cell a1 and b1 through columns B and C in sheet2 and when match is found will get total from column D Hope this helps "Sarah" wrote: Hi, What I am looking at cell A1 and B1 from Sheet1 and looking up those two dates in a table array on Sheet2. If both of those dates are found in lets say cell B2 and C2 then I want the value in that table on sheet2, cell D2 to be returned on Sheet1. Does this help at all? "Eduardo" wrote: Hi, An example will help but assuming that you want to extract a value from column C if the dates in column D and E match your dates entered in cells A1 and B1 and put the value in A2 use in A2 =sumproduct(--(A1=$D$1:$D$1000),--(B1=$E$1:$E$1000),$C$1:$C$1000) "Sarah" wrote: Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to lookup 2 different dates in a table and if both of those dates appear on one sheet, I need the associated value to be returned. If this is possible, how would I do this? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup 2 values to return 1
i think that worked. thanks!
"Glenn" wrote: The more information you provide up front, the easier it is to provide a working solution. Try this array formula (commit with CTRL+SHIFT+ENTER): =INDEX(Sheet2!$B$2:$D$1000,MATCH(A1&B1, Sheet2!$B$2:$B$1000&Sheet2!$C$2:$C$1000,0),3) Sarah wrote: That didnt work. The return value is actually a text so a sum wouldnt work. Thanks though! "Eduardo" wrote: Hi Sarah, try =sumproduct(--(A1=sheet2!$B$1:$B$1000),--(B1=sheet2!C$1:$C$1000),sheet2!$D$1:$D$1000) formula will look what you have in cell a1 and b1 through columns B and C in sheet2 and when match is found will get total from column D Hope this helps "Sarah" wrote: Hi, What I am looking at cell A1 and B1 from Sheet1 and looking up those two dates in a table array on Sheet2. If both of those dates are found in lets say cell B2 and C2 then I want the value in that table on sheet2, cell D2 to be returned on Sheet1. Does this help at all? "Eduardo" wrote: Hi, An example will help but assuming that you want to extract a value from column C if the dates in column D and E match your dates entered in cells A1 and B1 and put the value in A2 use in A2 =sumproduct(--(A1=$D$1:$D$1000),--(B1=$E$1:$E$1000),$C$1:$C$1000) "Sarah" wrote: Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to lookup 2 different dates in a table and if both of those dates appear on one sheet, I need the associated value to be returned. If this is possible, how would I do this? . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup 2 values and return a third | Excel Worksheet Functions | |||
| lookup 1 value and return multiple corresponding values | Excel Worksheet Functions | |||
| How do I use LOOKUP to return a range of values, then SUM values? | Excel Worksheet Functions | |||
| lookup mutliple values to return one value | Excel Worksheet Functions | |||
| How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) | |||
Linear Mode
