IF,ISNA VLOOKUP problem

[Guest]

Hi all

I have this formula, which works

The ABC is a named range containin
1 2 3 4
lookup value base rate unit rate minimum charg

so the formula looks lik

base rate (x1) + unit rate x value in cell N1
=IF(ISNA(VLOOKUP(I10,ABC,2,FALSE)+VLOOKUP(I10,ABC,3,FALSE)*N10),"",(VLOOKUP(I10,ABC,2,FALSE)+VLOOKUP(I10,ABC,3,FALSE)*N10)

What I need to do is create a further if statement to test if the result is less than the value in column 4, if it is = column 4 value, if it isn't it's the above statement

Follow!

thank

Alby

 

[A.W.J. Ales]

Alby,

In stead of another IF statement you can use the MAX() function. Max(a,b)
returns the larger of the two arguments.
So try :

=IF(ISNA(VLOOKUP(I10,ABC,2,FALSE)+VLOOKUP(I10,ABC,3,FALSE)*N10),"",MAX(VLOOK
UP(I10,ABC,2,FALSE)+VLOOKUP(I10,ABC,3,FALSE)*N10,VLOOKUP(I10,ABC,4,FALSE)))

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

Hi all,

I have this formula, which works!

The ABC is a named range containing
1 2 3 4
lookup value base rate unit rate minimum charge

so the formula looks like

base rate (x1) + unit rate x value in cell N10
=IF(ISNA(VLOOKUP(I10,ABC,2,FALSE)+VLOOKUP(I10,ABC,3,FALSE)*N10),"",(VLOOKUP(
I10,ABC,2,FALSE)+VLOOKUP(I10,ABC,3,FALSE)*N10))

What I need to do is create a further if statement to test if the result

is less than the value in column 4, if it is = column 4 value, if it isn't
it's the above statement.

 

[Guest]

Try This formulae in D1 cell. and then copy it down, it shall work

=if(IF(ISNA(VLOOKUP(I10,ABC,2,FALSE)+VLOOKUP(I10,ABC,3,FALSE)*N10),"",(VLOOKUP(I10,ABC,2,FALSE)+VLOOKUP(I10,ABC,3,FALSE)*N10))<D1,D1,=IF(ISNA(VLOOKUP(I10,ABC,2,FALSE)+VLOOKUP(I10,ABC,3,FALSE)*N10),"",(VLOOKUP(I10,ABC,2,FALSE)+VLOOKUP(I10,ABC,3,FALSE)*N10))


Regards

----- Alby wrote: -----

Hi all,

I have this formula, which works!

The ABC is a named range containing
1 2 3 4
lookup value base rate unit rate minimum charge

so the formula looks like

base rate (x1) + unit rate x value in cell N10
=IF(ISNA(VLOOKUP(I10,ABC,2,FALSE)+VLOOKUP(I10,ABC,3,FALSE)*N10),"",(VLOOKUP(I10,ABC,2,FALSE)+VLOOKUP(I10,ABC,3,FALSE)*N10))

What I need to do is create a further if statement to test if the result is less than the value in column 4, if it is = column 4 value, if it isn't it's the above statement.

Follow!!

thanks


Alby