Using Min and Max with a roundup and vlookup formula

[rrodri5101]

i've got a cell that i need teh minimum return to be $100 and maximum to be $500.

This is what the formula currently look like. Its using a roundup and vlookup formulas. It cell Q3 and R#

=ROUNDUP(VLOOKUP($L$2,'ANTIC Residential Rates'!$A$2:$Y$17159,3,TRUE)*10%,0)

 

[SanjayGMusafir]

i've got a cell that i need teh minimum return to be $100 and maximum to be $500.

Try this -

=Let(VL,Iferror(ROUNDUP(VLOOKUP($L$2,'ANTIC Residential Rates'!$A$2:$Y$17159,3,TRUE)*10%,0),0),Ifs(VL<=100,100,VL>=500,500,True,VL))

But I'm sure better than VLOOKUP would be XLOOKUP as it is less complicated and can handle 'No Result' situation by itself.

 

[rrodri5101]

Try this -

=Let(VL,Iferror(ROUNDUP(VLOOKUP($L$2,'ANTIC Residential Rates'!$A$2:$Y$17159,3,TRUE)*10%,0),0),Ifs(VL<=100,100,VL>=500,500,True,VL))

But I'm sure better than VLOOKUP would be XLOOKUP as it is less complicated and can handle 'No Result' situation by itself.

Thank you Sanjay that worked perfectly

i appreciate the help

 

[SanjayGMusafir]

Glad to help you. Would request you to mark the thread with your solution so that others may be benefitted.

 

[Fluff]

Another option is

=median(100,500,ROUNDUP(VLOOKUP($L$2,'ANTIC Residential Rates'!$A$2:$Y$17159,3,TRUE)*10%,0))