IF / Vlookup formula

[Peter]

Hi all

I have the following formula

=IF(ISNA(VLOOKUP(I14,Paid!A:B,2,FALSE)),IF($K14<1,5,3),(VLOOKUP(I14,Paid!A:B,2,FALSE)))

This is used to check if invoice no is on paid sheet then enter 1, if not
then depending on age of invoice (column K) then either 5 or 3.

I need to add another if function - =if(left(T14,8)"Promised",2 - this is to
show payments due -
=if(left(T14,8)"Promised",2,IF(ISNA(VLOOKUP(I14,Paid!A:B,2,FALSE)),IF($K14<1,5,3),(VLOOKUP(I14,Paid!A:B,2,FALSE))))

Conflicts when invoice is paid and invoice No appears on paid sheet - still
remains as 2.

Order of checking should be: - check if paid if yes then 1 - if no then
check if promised, if yes then 2, if no then check age, 5 if <1 3 if>1.

Any ideas? or if not best explained please let me know

 

[Bernie Deitrick]

Peter,

Try this:

=IF(NOT(ISNA(VLOOKUP(I14,Paid!A:B,2,FALSE))),VLOOKUP(I14,Paid!A:B,2,FALSE),IF(LEFT(T14,8)="Promised",2,IF($K14<1,5,3)))

HTH,
Bernie
MS Excel MVP

 

[Peter]

=IF(ISNA(VLOOKUP(I14,Paid!A:B,2,FALSE)),IF(LEFT(T14,8)="Promised",2,IF($K14<1,5,3)),(VLOOKUP(I14,Paid!A:B,2,FALSE)))

Got it!! Just had to re-arrange the brackets...always helps

 

[Peter]

Hi Bernie

Thank you for taking the time to help me on this one - but somehow I managed
it (just a matter of re-arranging the brackets...)

=IF(ISNA(VLOOKUP(I14,Paid!A:B,2,FALSE)),IF(LEFT(T14,8)="Promised",2,IF($K14<1,5,3)),(VLOOKUP(I14,Paid!A:B,2,FALSE)))