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

Do you think Amazon can bring Internet access to less-developed regions?

Discuss in The Lounge

Poll History