multiple vlookup; index/match
 

I fear my objective is not attainable using basic Excel commands but I'll
give it go.

I have a spreadsheet:
Tab 1
A B
Dated Date: 4/15/1998

Tab 2
A B C
3 1997 159.1 <-- 3 represents march, 1997 is year
4 1997 159.6
5 1997 160
6 1997 160.2
7 1997 160.1
8 1997 160.3
9 1997 160.5
10 1997 160.8
11 1997 161.2
12 1997 161.6
1 1998 161.5
2 1998 161.3
3 1998 161.6
4 1998 161.9
5 1998 162.2
6 1998 162.5
7 1998 162.8
8 1998 163
9 1998 163.2
etc. continuing to 2008

My objective is to match the month and year from Tab 1 to Tab 2 and bring
back the corresponding number in column C in Tab 2. For my example, I would
expect 161.9.

I attempted to use SUMPRODUCT but no luck. I am suspecting it is because I
have repeating numbers in column A. I briefly read the documentation on
index/match but do not think it is the appropriate command either.

Is it possible to match on multiple criteria when one column of critera is
not unique?

Many thanks.

multiple vlookup; index/match
 

Try this formula...

=SUMPRODUCT(--($A$1:$A$10=MONTH($G$1)),--($B$1:$B$10=YEAR($G$1)),$C
$1:$C$10)

Assumes your data starts in A1:C10 as formatted below with the date in
G1.



On Jun 2, 11:35 am, JE wrote:
I fear my objective is not attainable using basic Excel commands but I'll
give it go.

I have a spreadsheet:
Tab 1
A B
Dated Date: 4/15/1998

Tab 2
A B C
3 1997 159.1 <-- 3 represents march, 1997 is year
4 1997 159.6
5 1997 160
6 1997 160.2
7 1997 160.1
8 1997 160.3
9 1997 160.5
10 1997 160.8
11 1997 161.2
12 1997 161.6
1 1998 161.5
2 1998 161.3
3 1998 161.6
4 1998 161.9
5 1998 162.2
6 1998 162.5
7 1998 162.8
8 1998 163
9 1998 163.2
etc. continuing to 2008

My objective is to match the month and year from Tab 1 to Tab 2 and bring
back the corresponding number in column C in Tab 2. For my example, I would
expect 161.9.

I attempted to use SUMPRODUCT but no luck. I am suspecting it is because I
have repeating numbers in column A. I briefly read the documentation on
index/match but do not think it is the appropriate command either.

Is it possible to match on multiple criteria when one column of critera is
not unique?

Many thanks.

multiple vlookup; index/match
 

This post can be disregarded. I was able to successfully use INDEX.

"JE" wrote:

I fear my objective is not attainable using basic Excel commands but I'll
give it go.

I have a spreadsheet:
Tab 1
A B
Dated Date: 4/15/1998

Tab 2
A B C
3 1997 159.1 <-- 3 represents march, 1997 is year
4 1997 159.6
5 1997 160
6 1997 160.2
7 1997 160.1
8 1997 160.3
9 1997 160.5
10 1997 160.8
11 1997 161.2
12 1997 161.6
1 1998 161.5
2 1998 161.3
3 1998 161.6
4 1998 161.9
5 1998 162.2
6 1998 162.5
7 1998 162.8
8 1998 163
9 1998 163.2
etc. continuing to 2008

My objective is to match the month and year from Tab 1 to Tab 2 and bring
back the corresponding number in column C in Tab 2. For my example, I would
expect 161.9.

I attempted to use SUMPRODUCT but no luck. I am suspecting it is because I
have repeating numbers in column A. I briefly read the documentation on
index/match but do not think it is the appropriate command either.

Is it possible to match on multiple criteria when one column of critera is
not unique?

Many thanks.