Excel Formula

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

Been a long day today but for the life of me i cant work out the old version of this formula (i.e one that would yeild the same result in office xp, 2003)

Code:

=IFERROR(VLOOKUP(J9,'Control sheet'!$D$8:'Control sheet'!$F$10,3,FALSE),"0")

Anyone?

[Edit]
I tryed

Code:

=IF(ISERROR(VLOOKUP(J9,'Control sheet'!$D$8:'Control sheet'!$F$10,3,FALSE)),"0")

But this just returns a FALSE value so i've deffo missed something

 

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

can you upload the spreadsheet?

 

sorry bud its kind of populated with sensitive data(it's a pritty big file to), but i could provide any information on what i'm looking up and what type of value i want to return

 

heres a mock version if you open this with 2003 or lower you will notice that the "New Formula" will hit an error and prefix the formula (indicating that its not supported, the compat sheet will confim this) and the old one that am trying to get to work will say FALSE

 

q) Which version of excel are you using?

Is this a formula from a cell or written inside a macro

Vlookup is only used if you are comparing two excel spreadsheets, but you've only enclosed one?

 

A)I was writing in 2007 but client will be using 2003 didn't pick up on the error till i saved and got my functionality report

and it's in a cell

[Edit]
sorry i was doing it as per instructions from http://office.microsoft.com/en-us/excel/HP052093351033.aspx just mentions looking up within a range on a single sheet, the vlookup part seems to be working fine both return the correct value but i what to return a value of "0" if there nothing there

 

So are you trying to to find an error function in $D$8?
=IF(ISERROR(B4/C4),0,B4/C4) - trying to see what the equation above does.

Hmmm:hhhmmm

VLOOKUP searches for a value in the column of the mention cell/table and returns the value in a different column of the table in the same row where the searched value is found. However, if the value is not found in the mention column of the table, the VLOOKUP command will returns an error code.

 

Ahh right now I see,

I take it 2007 office doesn't have a conversion yet??

 

not that could see
the only alternative to the IFERROR function was ISERROR but the only 2 examples that i could find where =IF(ISERROR(VALUE)) and
=IF(ISERROR(Formula, "", Formula))

 

thanks for the replies but i think we may have cracked it

Code:

=IF(ISERROR(VLOOKUP(J9,'Control Sheet'!$D$8:'Control Sheet'!$F$10,3,FALSE)),"0",VLOOKUP(J9,'Control Sheet'!$D$8:'Control Sheet'!$F$10,3,FALSE))

still dont understand why it needs the formula twice

 

Excellent news glad you got it sorted in the end!

I must admit you've made me think twice about not getting Office 2007 if doesn't have backward compatibility for excel spreadsheets.

I am working on some projects for work at the moment on excel (office 2003) and was thinking of upgrading to office 2007.

I guess I will have to wait a while until there are some reviews on the web about it