vlooup with an if statement?????

[Guest]

Is it possible to do a vlookup with an if statement.
My spreadsheet looks like this:

Name 2003 2004 2005
John "formula" "formula" "formula"


My source looks like this:
Name Year Amount
John 2003 45
John 2005 354
Mary 2004 500

What I need to do is to have the vlookup go down the name list and if the
year equals the header in the top spreadsheet to return the amount.

Is this even possible??????

I need to use this data in a mail merge that MUST go out today.

Any and all suggestions are greatly appreciated and welcome.
Thanks

 

[Guest]

With your lookup data in cells L2:N4, and the names to lookup in column A,
the years in row 1, use this array formula (enter it by pressing
Ctrl-Shift-Enter)

=IF(ISERROR(INDEX($N$2:$N$4,MATCH($A2&B$1,$L$2:$L$4&$M$2:$M$4,0))),0,INDEX($N$2:$N$4,MATCH($A2&B$1,$L$2:$L$4&$M$2:$M$4,0)))

 

[PCLIVE]

You could do this with SUMPRODUCT.

Assuming the source is in A6 through C8 (without headers).

=SUMPRODUCT(--($A$6:$A$8=$A2),--($B$6:$B$8=B$1),$C$6:$C$8)

HTH,
Paul