INDEX - MATCH with three variables

[Guest]

Column 1 contains the Year and Month for each currency.
Column 2 contains each currency, repeated for each year-month group.
Columns 3 through 33 contain the spot rate for each day of the month

I can set up a formula to capture two of the three above variables but run
into trouble when trying to insert a third condition. The below is a sample
of where I can get. The problem is that it finds the first occurrence of the
currency. I need to be able to add the month-year column into the equation.

=INDEX(Sheet5!$A$1:$AG$10000,MATCH(G2,Sheet5!$B$1:$B$10000,0),MATCH(D2,Sheet5!A1:AG1,0))

1
YRMONTH CCY SPOTRATE1_8
200702 AED 3.67265
200702 ARS 3.10375
200702 ATS 10.56940
200702 AUD 0.77375
200702 BBD 2.00000
200702 BEF 30.98180
200702 BMD 1.00150

Thanks for any help you can give.

 

[Peo Sjoblom]

One way


=INDEX(E1:K9,MATCH(1,(E1:E9=L1)*(F1:F9=M1),0),MATCH(N1,G2:N2,0))


array entered with ctrl + shift & enter

you might find this might slow down the workbook considerable since it is an
array formula

but that is one way to get a result where the first MATCH would use the (in
your case) currency AND the month/year and the second MATCH is left
unchanged

 

[Bob Phillips]

assuming the year/month is in C2

=INDEX(Sheet5!$A$1:$AG$10000,MATCH(1,(C2=Sheet5!$A$1:$A$10000)*(G2=Sheet5!$B$1:$B$10000),0),MATCH(D2,Sheet5!A1:AG1,0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Arvi Laanemets]

Hi

=SUMPRODUCT(--(Sheet5!$A$1:$A$10000=200702),--(Sheet5!$B$1:$B$10000="AED"),Sheet5!$C$1:$C$10000)
, or, when YRMONTH values are strings
=SUMPRODUCT(--(Sheet5!$A$1:$A$10000="200702"),--(Sheet5!$B$1:$B$10000="AED"),Sheet5!$C$1:$C$10000)

Different conditions you may add into formula are limited only by number of
characters Excel allows for formulas. All ranges in formula must be of same
dimension. You may use SUMPRODUCT for 2 different tasks:
=SUMPRODUCT(--(Range1=Condition1),--(Range2=Condition2),...,--(RangeN=ConditionN))
(the formula counts the number of occurrences where all conditions are
True);
=SUMPRODUCT(--(Range1=Condition1),--(Range2=Condition2),...,--(RangeN=ConditionN),RangeX)
(the formula sums all values in range RangeX, for which all conditions are
True)


Arvi Laanemets