MATCH Function - 2 Dimension Arrays

[AZ_Ray]

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,

 

[Mike H]

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]

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.

 

[Peo Sjoblom]

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