Double VLOOKUP in Excel !!!

[swamit]

Hey folks,
I have data in several columns and a gazillion rows...
I want to lookup data from another sheet to match certain column pairs...
Like - In column B i have "week number" and in C I have "year" - lets say in cell B6 I have "Week 6" and in cell C6 I have "2002" - then I want to lookup those 2 cells in another set of columns X and Y...
Vlookup does not take "AND" or "&" or "+" - what can I do to lookup 2 columns ?
Thanks in advance...
Amit

 

[Hasit]

Can you send me an example file to hasitbakhda@yahoo.co.uk

I understand what you are trying to do, but data will make it easier for me to create a formula for you.

 

[swamit]

Sorry Hasit...
I did realize that my question was a bit confusing.
Here is what I want to do....
Look up Columns A and B and find the matching total from column Z to be entered in Column C

<A> <B> <C>
Week Year Total
1 2000
2 2000
3 2000
4 2000
5 2000
1 2001
2 2001
3 2001
4 2001
5 2001
1 2002
2 2002
3 2002
4 2002
5 2002


<X> <Y> <Z>

Week Year Total
10 2000 31
10 2002 32
9 2000 35
7 2000 41
8 2000 42
8 2002 45
9 2002 45
2 2000 47
3 2000 48
6 2000 52
10 2001 54
4 2000 56
1 2002 57
1 2000 58
2 2002 58
5 2000 59
4 2001 59
3 2002 59
9 2001 62
3 2001 67
2 2001 68
1 2001 69
6 2001 82
8 2001 92
7 2001 95
5 2001 98
4 2002 98
5 2002 98
6 2002 100
7 2002 101


Thanks
-amit-

 

[GlennUK]

Why don't you try this, which is a SUM(IF formula entered as an array ( with Ctrl-Shift-Enter instead of Enter ) ...


=SUM(IF($X$2:$X$31=A2,IF($Y$2:$Y$31=B2,$Z$2:$Z$31,0),0))

in C2, and then copy the formula down as required.

Glenn.

 

[Hasit]

I think Glenn has got it. I was going to use the conditional sum wizard to help contrsuct the formula. I got the same result as the formula GlennUK suggests above.