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!

 

[strewth78]

Thanks.

I took what you suggested and then just modified the statement to the last IF function. That way if it returns zero or fails to find a result in the vlookup it moves to thefinal IF function.

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


Thanks again to all the help from this thread.

 

[Aladin Akyurek]

@strewth78

Is your VLOOKUP part expected to return a number or just text when successful?