vlookup

[ghia88]

Hi,

do you know how can i do to retunr a 0 value if something it's not present in my clomn look up...?
at the moment i have
=VLOOKUP(I5,'Exposure'!$A$5:$D$18,4,0)

=IF(VLOOKUP(J5,'Exposure'!$A$5:$D$18,1,0)="ILS",vlooukup(J5,'Exposure'!$A$5:$D$18,4,0),"0")

so basically i m looking into a different tab for currencies and with a vollokup retunring the amount, but if the currency is not there at the moment reports me NA

thanks

 

[Desu Nota from Columbus]

=IF(ISERROR(VLOOKUP(I5,'Exposure'!$A$5:$D$18,4,0)),0,VLOOKUP(I5,'Exposure'!$A$5:$D$18,4,0))


This says if your vlookup returns an error (#N/A, etc.) it will return a 0 instead.

 

[moonfish]

If you are using excel 2007 or later, you can use the IFERROR command:
=iferror(VLOOKUP(I5,'Exposure'!$A$5:$D$18,4,0),0)

This will replace any error you receive from the vlookup with the specified value, in this case '0'.

 

[mowgli212001]

you can try wrapping an iserror around it

=if(iserror(IF(VLOOKUP(J5,'Exposure'!$A$5:$D$18,1,0)="ILS",vlooukup(J5,'Exposure'!$A$5:$D$18,4,0),"0")),0,IF(VLOOKUP(J5,'Exposure'!$A$5:$D$18,1,0)="ILS",vlooukup(J5,'Exposure'!$A$5:$D$18,4,0),"0"))

 

[Desu Nota from Columbus]

Welcome to the Forum!

 

[ghia88]

it works

ihave 2007 version thanks