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]

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,"-")))

Sorry Ali. As i said above it returns a FALSE result, no matter what is in w7

 

[AliGW]

What about this?

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

 

[strewth78]

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).

If there is a value in the vlookup it returns it. But when I empty that cell the result becomes zero.

 

[strewth78]

What about this?

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

I unfortunately still get the same FALSE result.

 

[AliGW]

Then it must be something to do with your data. Are you able to provide a DropBox link or similar to the workbook?

 

[strewth78]

I thought If the cell was empty in the vlookup them it would fail to return anything, but it's returning zero for an empty cell. I think I need to add a part so that if the vlookup returns a number less than 1 then it should move on to the following IF.

 

[AliGW]

OK.

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

 

[strewth78]

This last one gets an error in the formula.

 

[strewth78]

In fact it was identical to the one I just tried myself. I think it's right but just not quite written right.

 

[eduzs]

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

If G column is "", then it will return 0, that's correct for the function, and there's nothing you can do, only avoid this 0 with a statment.