VLOOKUP with IF statement

Microsoft Office 2007 home and student
June 17, 2010 at 12:47:14
Specs: Windows Vista
I am trying to create a formula based on the results of a vlookup.

I am entering the formula into a cell in one workbook and pulling information from a second book.

I want to see the results of a formula (based on other cells) but only if this same person has a checkmark beside their name in another workbook (shown as the letter "a")

The formula I have muddled so far is:
=IF(VLOOKUP(C11,'YEAR END 2010.xlsx'!ContractFunding,10,),J11/N11*O11-P11,0)

If there is no checkmark, then I want nothing to appear in this cell. If there is, then I want the result of the calculation.

I would appreciate any help you can give with this. I've been staring at it too long.

See More: VLOOKUP with IF statement

Report •

June 17, 2010 at 14:11:33
It appear that you've almost got it:

The format of a =IF() statement is:

=IF(logical_test, value_if_true, value_if_false)

Your logical_test needs to be TRUE or FALSE,

So this should work:

VLOOKUP(C11,'YEAR END 2010.xlsx'!ContractFunding,10,)="a",

has a checkmark beside their name in another workbook (shown as the letter "a")

Is it the lower case letter A or a special character?

If it is a special chracter, you will need to check for that and not a lower case A.



Report •

June 18, 2010 at 08:24:15
Hi Mike,

Thanks for taking the time to help me out with this.

By entering the formula with your corrections, it came back with a TRUE result.

So that part worked great. (FYI it is a lower case letter a, the font in the cell is formatted as webdings. I am open for other suggestions if they would make things easier).

So now for the second part of the equation...

IF it comes back as TRUE, I need it to give me the result of the formula J11/N11*O11-P11.

If it comes back as FALSE, then I want the cell to show as blank/empty...

I look forward to your response.

Report •

June 18, 2010 at 08:39:11
What Mike was trying to say was that your original IF statement wasn't really checking for anything. In other words, you didn't have a logical test, which he provided.

=If(VLOOKUP(C11,'YEAR END 2010.xlsx'!ContractFunding,10,)="a",
value_if_true, value_if_false)

or, in your case,

=IF(VLOOKUP(C11,'YEAR END 2010.xlsx'!ContractFunding,10,)="a",
J11/N11*O11-P11, 0)

More specifically, to return a blank cell use "" instead of 0

=IF(VLOOKUP(C11,'YEAR END 2010.xlsx'!ContractFunding,10,)="a",
J11/N11*O11-P11, "")

Report •

Related Solutions

June 18, 2010 at 09:10:08

You have both been a huge help. I've been pulling my hair with formulas all week and this one stumped me.

It worked great!

Thanks again, and have a great weekend!

Report •

June 18, 2010 at 09:23:36
The main purpose of our existence is to help people keep their hair.

I'm glad we could help.

Report •

Ask Question