multiple vlookup; index/match

J

JE

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.
 
T

Tim879

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top