vlookup with an if statement

[cheyenne2002]

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
Sharon (totally stressed today)

 

[xlbo]

No need for an if statement. If I understand correctly, the following should work:

=VLOOKUP(A11,DataSheet!$A$2:$D$200,-2001+B11,FALSE)

Where your source NAME (ie John) is in A11, your YEAR is in B11. The formula would go in C11

Assumes that your data sheet is set out as above with names in col A and years 2003-2005 in columns B-D

All it is doing is choosing how many columns to look up across by adding the year to -2001. This will return a 2 for 2003, a 3 for 2004 and a 4 for 2005 which are the lookup constants needed for your layout

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[cheyenne2002]

Geoff,

Something is not working. I'm sure it is my understanding of the formula.

My source is on one sheet (sheet1) and looks like this (actual capture)


A B C
DonorName CampaignName Received
3M 2005 10000
3M 2006 10000
A & B Plaster 2005 250
A & B Plaster 2006 250
A & G Concrete 2005 500


My sheet (sheet2) where I want to put the formula is like this.

A B C D E F
DonorName 2003 2004 2005 2006 2007
3M
A & B Plaster
A & G Concrete

I need the end result to look like this:
A B C D E F
DonorName 2003 2004 2005 2006 2007
3M 10000 10000
A & B Plaster 250 250
A & G Concrete 500



I have tried the following formulas and get errors.

=VLOOKUP(A2:A476,Sheet1!G1:I896,3)
=INDEX(A2:F476,MATCH(B1,Sheet1!G1:I896,3))
=IF(Sheet1!B2=Sheet2!B1,VLOOKUP(A2:A476,Sheet1!A1:C895,3,"None"))

This is how I translated the formula you gave me. Can you please let me know what I did wrong?
=VLOOKUP(A2,Sheet1!$A$2:$C$895,-2001+B1,FALSE)

I know it is something I'm doing wrong or not understanding, could be the stress to finish this so I can do the mail merge.
Problem is I have all the data in an Access database (barely working properly) and I can't get it out the way I need it.

Thanks for your help.
Sharon

 

[yogia]

Hi Sharon:

Let me see if I understood you correctly ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[xlbo]

aaah sorry - read it the wrong way round

If you can add another column to the data where your values are being held it would make your formula a lot easier

=A2&B2

in column C will concatenate the DonorName & Year

With the values in col D

=VLOOKUP($A2&C$1, Sheet1!$C$2:$D$896,2,false)

should do what you want

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[cheyenne2002]

Yodi,

This is sort of working....it is giving me a dollar amount but it is giving me the amount from the wrong donor.

example: 3M did not donate in 2003 and the formula searches down the list till it comes to the first occurance of a 2003 donation and returns that amount for 3M in 2003.

this is my exact formula. do you see anything wrong with it?

=INDEX(Sheet1!$C$2:$C$895,IF(Sheet1!$A$2:$A$895=$A2,MATCH(B$1,Sheet1!$B$2:B$895,0)))

Thank you for checking.
Sharon

 

[xlbo]

Sharon - have you cheked out my idea ? should work fine as long as you can add the extra column in

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[cheyenne2002]

Vogi,

Upon further looking at the formula, when I copied it down the column, it seems to be giving me random data returns. when I copy it to the right I just get #N/A.

Sharon

 

[cheyenne2002]

Geoff,

I'm trying yours now. I'm trying to understand the concept.

I do not understand why I would want to concatenate the name and the year into one cell.

Can you further explain how and why this would work to get the results I need.

Thanks

 

[xlbo]

Your lookup is based on both the name of the donor and the year which they donated. If you concatenate the 2, you get a result like:

3M 2004 3M2004 10000

in your base data sheet

You have data laid out like in your results sheet

DonorName 2003 2004 2005
3M
A & B Plaster

For a lookup to work, you must have matching data. Since you have 2 criteria to match on a lookup will not work. When you concatenate the 2 bits of data, you effectively create one combined criteria which the lookup can work with.

The vlookup formula:
=VLOOKUP($A2&B$1, Sheet1!$C$2:$D$896,2,false)
references the donor name (A2) and the Year (C1) to create a set of criteria that matches with your concatenated column in the data sheet thus allowing the lookup to work

Alternatively, keeping your data as it is, you can use SUMPRODUCT

=sumproduct((Sheet1!$A$2:$A$895=$A2)*(Sheet1!$B$2:$B$895=B$1)*($C$2:$C$895))


But this is quite a calculation intensive formula so if you have quite a long list, you may be better off adding the extra column and using vlookup

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[cheyenne2002]

Ah Ha! I get it. I can have vlookup check two separate cells and see if the combined data in those two cells equals one cell in my data source....

My only problem now is the #N/A for those that do not return data and I have a lot of them.

Any suggestions.
Sharon

 

[yogia]

Hi Sharon:

I saw you have posted another example ... so here we go:

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[xlbo]

=if(isna(VLOOKUP($A2&B$1, Sheet1!$C$2:$D$896,2,false)),0,VLOOKUP($A2&B$1, Sheet1!$C$2:$D$896,2,false))

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question