• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

What's wrong with this formula?

J

Jaine Wills

New Member
=IF(or(M4=Blank,"",N4=blank,"",or(O4=blank,"",or(P4=blank,"",or(Q4=blank,""),vlookup(Sheet3!A4:C21,3,false)

I'm stumped! It "should" work but it doesn't.
 
Hi Jaine,
Can you describe in English what you are hoping the formula would do? That would help us give you better direction than attempting to decipher your formula above.

Cheers,
Sajan.
 
Jaine

It looks like you haven't pasted all the formula or it has been truncated as the aren't enough closing )'s

Possibly =IF(or(M4="",N4="",O4="",P4="",Q4=""), Function if True, Function if False)

Also the vlookup(Sheet3!A4:C21,3,false) function should be: vlookup(Lookup_Value, Sheet3!A4:C21,3,false)
the Lookup_Value can be a value or reference
 
Hi Jaine,

Without more information, we have a hard time helping you. But just to give it a go, here is my take on the formula you gave:
Code: 
=IF(LEN($M4&$N4&$O4&$P4)>0,VLOOKUP(A2,Sheet3!$A$4:$C$21,3,FALSE),"")
The "A2" is the Lookup_Value, change this as needed :)
 
Sajan - this is the same thing as I am trying to do in the extract I posted on another thread. I was trying to have a go myself!

All - I have a number of cells that 'could' contain data; this would be based on what benefit they were awarded. At the moment the financial impact of the benefit is being calculated manually (!!! madness with the capabilities of Excel). So, in English, this is what I "thought" I'd written:

If M4 is blank, move onto N4 - if this is blank move onto O4 and so on until the end of the string. When there is an entry in one (possible max of two) of the cells (M4:Q4) I want Excel to lookup the value in a table on another sheet and calculate the value of the benefit. Ideally, I would also like it to calculate the overall value based on the number of years the benefit has been awarded for.

I thought I was pretty good at excel until I started getting Chandoo's bulletins: I know nothing! :)
 
Hi, Jaine Wills!

This part of Xiq's cleaned version of your formula:
LEN($M4&$N4&$O4&$P4)>0
or this mine an array formula:
MAX(LEN(M4:Q4))>0
evaluate to true if anything is entered in that range, so I'd give a try to Xiq full normal (non-array) formula ot to my array formula:
=IF(MAX(LEN(M4:Q4))>0>0,VLOOKUP(A2,Sheet3!$A$4:$C$21,3,FALSE),"")
both should work.

2 things:
a) If you have to deal with a short number of cells, go with Xiq; otherwise go with the array formula
b) Remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter.

Regards!
 
=IF(OR(M4="",N4="",O4="",P4="",Q4=""),VLOOKUP(lookup_value,tbble_array,col_index_num,FALSE),"")

or why not just upload the sample file?

Azumi
 
You must log in or register to reply here.
Back
Top