help needed with complicated multiple IF summation

[strewth78]

I want to look into cell W7. If it is a number, then show that number.
if not, then i want to do a vlookup(D7,'2017'!A:G,7,).
However if there is no number in that vlookup, then go back to W7...
if W7 says 'pre-arb' then display number 535000.
if it doesnt say pre-arb, then display '-'

here is what ive tried...

=IF(ISNUMBER(W7),W7,IFERROR(VLOOKUP(D7,'2017'!A:G,7,),IF(W7="pre-arb",535000,"-")))

I can see it gets stuck after the isnumber function. so if there is a number in W7 it produces it, if not it returns zero. Im sure im just messing up the iferror part somehow, but ive become lost at what it is... probably something simple.

any help? much appreciated!

 

[eduzs]

Set the start/end row, like:

vlookup(D7,'2017'!A1:G100,7,).

 

[strewth78]

the vlookup on its own was functioning fine.
i had also used it with the iferror, like so...

=IFERROR(VLOOKUP(D7,'2017'!A:G,7,),"-")

 

[AliGW]

Try this:

=IF(ISNUMBER(W7),W7,IF(ISERROR(VLOOKUP(D7,'2017'!A:G,7,)),IF(W7="pre-arb",535000,"-")))

 

[eduzs]

=IF(W7="pre-arb",535000,IF(ISNUMBER(W7),W7,IFERROR(VLOOKUP(D7,'2017'!A:G,7,),"-")))

 

[strewth78]

Try this:

=IF(ISNUMBER(W7),W7,IF(ISERROR(VLOOKUP(D7,'2017'!A:G,7,)),IF(W7="pre-arb",535000,"-")))

this returns a FALSE result.

 

[strewth78]

=IF(W7="pre-arb",535000,IF(ISNUMBER(W7),W7,IFERROR(VLOOKUP(D7,'2017'!A:G,7,),"-")))

the "pre-arb" part of the summation needs to come last, only if it results nothing from the other 2 IF functions.

 

[AliGW]

Did you try my suggestion?

 

[eduzs]

the only way your formula returns 0 is if W7 value = 0 or vlookup returns 0 (D7 value in A column corresponds to 0 value in G column in 2017 sheet).

 

[AliGW]

I'll try again, as my posts seem to have been missed.

Try this:

=IF(ISNUMBER(W7),W7,IF(ISERROR(VLOOKUP(D7,'2017'!A:G,7,)),IF(W7="pre-arb",535000,"-")))