Combine IF with Vlookup

[Keshy85]

Good day,

I am using the below formula to return a value, however need to add an IF statement to say if the number is greater than or equal to "5", it should return "5" if not greater or equal to then provide the number from the vlookup that is below "5"

=IF(ISERROR(VLOOKUP($B$4;'Combined AM2'!$B$427:$P$463;3;FALSE));"0";VLOOKUP($B$4;'Combined AM2'!$B$427:$P$463;3;FALSE))

Thanks.

[AliGW]

Try this:

=IF($B$4>=5;5;IF(ISERROR(VLOOKUP($B$4;'Combined AM2'!$B$427:$P$463;3;FALSE));0;VLOOKUP($B$4;'Combined AM2'!$B$427:$P$463;3;FALSE)))

or this:

=IF($B$4>=5;5;IFERROR(VLOOKUP($B$4;'Combined AM2'!$B$427:$P$463;3;FALSE));0))

Maybe this is what you want:

=MAX(5;IFERROR(VLOOKUP($B$4;'Combined AM2'!$B$427:$P$463;3;FALSE));0))

Why are you using the semi-colon as a delimiter in SA? I thought SA used the comma - are you using a European locale on your machine?

[Keshy85]

Good day Ali,

Thank You for the help, however they are not working. I need the vlookup to be done first before the IF statement.

[AliGW]

This?

=IF(IFERROR(VLOOKUP($B$4;'Combined AM2'!$B$427:$P$463;3;FALSE);0)>=5;5;IFERROR(VLOOKUP($B$4;'Combined AM2'!$B$427:$P$463;3;FALSE);0)

???

Would be the same as:

=MIN(5;IFERROR(VLOOKUP($B$4;'Combined AM2'!$B$427:$P$463;3;FALSE));0))

[Keshy85]

Thank You so Much Ali. Working perfectly. Much Appreciated!

[AliGW]

You're welcome, but why are you using semi-colons in South Africa??? What is your locale?