Mixing an IFNA, a vlookup & an 'AND'?

[Weaselwithagun]

Hi all,

I've done a vlookup which has three outcomes - one is that there's nothing to find so it returns #N/A, the second is that it finds a value that's less than zero & the third finds a value greater than zero.

I only want the positive figures to be returned from this formula, if its negative or not there i want it to say 0.00

I can make individual formulas that will answer one of the two issues (i.e. IFNA(Vlookup......), or IF(VLOOKUP.....>0....), but how do i combine the both into one?

I had a look at trying to add an 'AND' into the mix, but I've obviously not grasped the concept of this function.

Any help greatly appreciated.

Thanks,

Weasel.

[Roel Jongman]

Use MAX function to suppress negative values instead of an IF function

=IFNA(MAX(0,VLOOKUP(.....)),0)

[Weaselwithagun]

I've obviously entered this incorrectly, as it says i've entered too few arguments like this:

=IFNA(MAX(0,VLOOKUP(A2,'[Book1]Vendors Debit Balances'!$A$2:$H$1159,8,0)))

What've i done wrong?

[Pepe Le Mokko]

Still using XL2010 ? Then IFNA will not work ( did not exist yet)
Try IF(ISNA....
or IFERROR(...
( the last might hide any error which may not be what you need)

[Roel Jongman]

it needs a value for the IFNA to use when the MAX/VLOOKUP combo results in the #N/A error

you were very close

Please Login or Register  to view this content.

[Weaselwithagun]

Ah of course - thanks again this is perfect

[Pepe Le Mokko]

Then perhaps update your profile XL version?

[Weaselwithagun]

apologies, id forgotten that was there - updated now

[Pepe Le Mokko]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.