Vlookup - ISNA function
 

I have the following information
A B C D E
41000-100 41000-100 41000 10
In column E I need the information from other tab, but if column C is <50000
I need to bring in the number as negative otherwise as it
I have the formula as follow but is not working and ignore if column C is
less than 50000

=+IF(OR(ISNA(C4<50000),ISNA(-VLOOKUP(B4,'BM vs
Accpac'!A:C,3,FALSE))),0,VLOOKUP(B4,'BM vs Accpac'!A:C,3,FALSE))

Please any help will be appreciatte, thank you

Vlookup - ISNA function
 

There might be a better way, but I think this will work:

=IF(ISERROR(IF(C4<50000,-VLOOKUP(B4,'BM vs
Accpac'!A:C,3,FALSE),VLOOKUP(B4,'BM vs Accpac'!A:C,3,FALSE))),"Not
Found",IF(C4<50000,-VLOOKUP(B4,'BM vs Accpac'!A:C,3,FALSE),VLOOKUP(B4,'BM vs
Accpac'!A:C,3,FALSE)))

Tom

Vlookup - ISNA function
 

Let's start with this (in place of your code)

=IF(C4<50000,-VLOOKUP(B4,'BM vs Accpac'!A:C,3,FALSE),VLOOKUP(B4,'BM vs
Accpac'!A:C,3,FALSE))


"Eduardo" wrote:

I have the following information
A B C D E
41000-100 41000-100 41000 10
In column E I need the information from other tab, but if column C is <50000
I need to bring in the number as negative otherwise as it
I have the formula as follow but is not working and ignore if column C is
less than 50000

=+IF(OR(ISNA(C4<50000),ISNA(-VLOOKUP(B4,'BM vs
Accpac'!A:C,3,FALSE))),0,VLOOKUP(B4,'BM vs Accpac'!A:C,3,FALSE))

Please any help will be appreciatte, thank you

Vlookup - ISNA function
 

Thank you very much Tom, it worked

"TomPl" wrote:

There might be a better way, but I think this will work:

=IF(ISERROR(IF(C4<50000,-VLOOKUP(B4,'BM vs
Accpac'!A:C,3,FALSE),VLOOKUP(B4,'BM vs Accpac'!A:C,3,FALSE))),"Not
Found",IF(C4<50000,-VLOOKUP(B4,'BM vs Accpac'!A:C,3,FALSE),VLOOKUP(B4,'BM vs
Accpac'!A:C,3,FALSE)))

Tom

Vlookup - ISNA function
 

Try this

=IF(C4<50000,-VLOOKUP(B4,'BM vs Accpac'!A:C,3,FALSE),VLOOKUP(B4,'BM vs
Accpac'!A:C,3,FALSE))


On Sep 24, 9:41*am, Eduardo wrote:
I have the following information
A * * * * * * * * * * *B * * * * * * * * C * * * * *D * * * * * E
41000-100 *41000-100 * *41000 * * * 10
In column E I need the information from other tab, but if column C is <50000
I need to bring in the number as negative otherwise as it
I have the formula as follow but is not working and ignore if column C is
less than 50000

=+IF(OR(ISNA(C4<50000),ISNA(-VLOOKUP(B4,'BM vs
Accpac'!A:C,3,FALSE))),0,VLOOKUP(B4,'BM vs Accpac'!A:C,3,FALSE))

Please any help will be appreciatte, thank you