Microsoft Office 2007 home and student

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.

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.

MIKE

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.

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, "")

THANK YOU, THANK YOU, THANK YOU!!! 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!

The main purpose of our existence is to help people keep their hair. I'm glad we could help.

Ask Your Question

Weekly Poll