Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCH Function - 2 Dimension Arrays
According to kbhowtomaster KB275170 (HOW TO: Perform a Two-Dimensional Lookup
in Excel 2000) the following MATCH statement is possible: MATCH(E2&F2,A2:A4&B2:B4,0) Does this only work in Excel 2000? It does not work in Excel 2003. I would like to lookup a value based on 2 criteria from two columns, as follows: Find Amount for Resource 000 for the month of 200807 (July 2008) resource amount month 222 463.12 200801 000 1039.28 200807 000 87.99 200801 222 197.46 200807 444 60.79 200807 444 50 200801 888 -174077 200801 888 1995.59 200803 Thank you, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCH Function - 2 Dimension Arrays
Hi,
Try this =SUMPRODUCT((A1:A20=0)*(MONTH(C1:C20)=7)*(B1:B20)) Note this assumes Column A is 0 (Numeric) and not text "000" and that the dates in column C are correctly formatted. If either/both of those assumptions are incorrect then post back describing what formats are in columns A & C Mike "AZ_Ray" wrote: According to kbhowtomaster KB275170 (HOW TO: Perform a Two-Dimensional Lookup in Excel 2000) the following MATCH statement is possible: MATCH(E2&F2,A2:A4&B2:B4,0) Does this only work in Excel 2000? It does not work in Excel 2003. I would like to lookup a value based on 2 criteria from two columns, as follows: Find Amount for Resource 000 for the month of 200807 (July 2008) resource amount month 222 463.12 200801 000 1039.28 200807 000 87.99 200801 222 197.46 200807 444 60.79 200807 444 50 200801 888 -174077 200801 888 1995.59 200803 Thank you, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCH Function - 2 Dimension Arrays
Mike,
Thank you. Wow! That wasn't the answer I was looking for. I was hoping to use the MATCH function per MicroSoft KBA writeup which BTW doesn't work with Excel 2003. I suspect that the article is in error. I did check your formula and unfortunately could not get it to work. Big Thanks for the creative way of selecting a (row) value based on two different row criteria. "Mike H" wrote: Hi, Try this =SUMPRODUCT((A1:A20=0)*(MONTH(C1:C20)=7)*(B1:B20)) Note this assumes Column A is 0 (Numeric) and not text "000" and that the dates in column C are correctly formatted. If either/both of those assumptions are incorrect then post back describing what formats are in columns A & C Mike "AZ_Ray" wrote: According to kbhowtomaster KB275170 (HOW TO: Perform a Two-Dimensional Lookup in Excel 2000) the following MATCH statement is possible: MATCH(E2&F2,A2:A4&B2:B4,0) Does this only work in Excel 2000? It does not work in Excel 2003. I would like to lookup a value based on 2 criteria from two columns, as follows: Find Amount for Resource 000 for the month of 200807 (July 2008) resource amount month 222 463.12 200801 000 1039.28 200807 000 87.99 200801 222 197.46 200807 444 60.79 200807 444 50 200801 888 -174077 200801 888 1995.59 200803 Thank you, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCH Function - 2 Dimension Arrays
Use
=SUMPRODUCT(--(A1:A20=0),--(C1:C20=200807),B1:B20) Mike didn't noticed that you are not using dates in the month column -- Regards, Peo Sjoblom "AZ_Ray" wrote in message ... Mike, Thank you. Wow! That wasn't the answer I was looking for. I was hoping to use the MATCH function per MicroSoft KBA writeup which BTW doesn't work with Excel 2003. I suspect that the article is in error. I did check your formula and unfortunately could not get it to work. Big Thanks for the creative way of selecting a (row) value based on two different row criteria. "Mike H" wrote: Hi, Try this =SUMPRODUCT((A1:A20=0)*(MONTH(C1:C20)=7)*(B1:B20)) Note this assumes Column A is 0 (Numeric) and not text "000" and that the dates in column C are correctly formatted. If either/both of those assumptions are incorrect then post back describing what formats are in columns A & C Mike "AZ_Ray" wrote: According to kbhowtomaster KB275170 (HOW TO: Perform a Two-Dimensional Lookup in Excel 2000) the following MATCH statement is possible: MATCH(E2&F2,A2:A4&B2:B4,0) Does this only work in Excel 2000? It does not work in Excel 2003. I would like to lookup a value based on 2 criteria from two columns, as follows: Find Amount for Resource 000 for the month of 200807 (July 2008) resource amount month 222 463.12 200801 000 1039.28 200807 000 87.99 200801 222 197.46 200807 444 60.79 200807 444 50 200801 888 -174077 200801 888 1995.59 200803 Thank you, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract or report multiple arrays if a cell match a criteria | Excel Discussion (Misc queries) | |||
Looking up data by row and column (Arrays, Lookups, Index, Match?? | Excel Worksheet Functions | |||
MATCH being sent two arrays | Excel Worksheet Functions | |||
Use of offset and match functions with changing arrays, I think??? | Excel Worksheet Functions | |||
Using the sum function in a 3 dimension environment | Excel Discussion (Misc queries) |