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,

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,

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,

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,