if function

[Rik Smith]

Hi all


I'm trying to estimate the compensation if stolen it will be based on the percentages in column T the age of the vehicle will the the criteria so for vehicles less than one year old 50% of column K the value of the vehicle and so on as shown in columns R-T.

 

[Luke M]

Thankfully, you've got the perfect lookup table setup.
Formula in M5:
=LOOKUP(E5,$R$6:$T$10)*K5

Copy down as desired.

 

[Mike H..]

Hi all


I'm trying to estimate the compensation if stolen it will be based on the percentages in column T the age of the vehicle will the the criteria so for vehicles less than one year old 50% of column K the value of the vehicle and so on as shown in columns R-T.

Hi,

In M5 and drag down

=VLOOKUP(E5,$R$6:$T$10,3)*K5

 

[Rik Smith]

Thanks, I forgot to mention that if column I is TPO it should be multiplied by 0

 

[Luke M]

Tacking on a little bit then...
=LOOKUP(E5,$R$6:$T$10)*K5*(I5<>"TPO")

 

[Ratan Bhushan]

Hi,

In M5 and drag down

=VLOOKUP(E5,$R$6:$T$10,3)*K5

Mike can you please advise this vlookup working in which way.... how this checking the data between a range like how it is checking that 2 is falling between 2-4...

 

[Mike H..]

Mike can you please advise this vlookup working in which way.... how this checking the data between a range like how it is checking that 2 is falling between 2-4...

Hi,

VLOOKUP has the following arguments and the last argument in bold below is optional. If the range_lookup argument is set to FALSE then vlookup finds an exact match or returns an error.

If the argument is TRUE or (as in my formula) omitted then vlookup returns an exact match or if there isn't an exact match it returns the largest match that is less then the lookup value and that's how in the formula it's working out whether the value is between values.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

 

[Ratan Bhushan]

Hi,

VLOOKUP has the following arguments and the last argument in bold below is optional. If the range_lookup argument is set to FALSE then vlookup finds an exact match or returns an error.

If the argument is TRUE or (as in my formula) omitted then vlookup returns an exact match or if there isn't an exact match it returns the largest match that is less then the lookup value and that's how in the formula it's working out whether the value is between values.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

you are awesome, thanks...