HLOOKUP
Finds the value specified (called lookup value) in the first row of the range specified and gets the corresponding figure from specific row in the same column where lookup value is found
Finds the value specified (called lookup value) in the first row of the range specified and gets the corresponding figure from specific row in the same column where lookup value is found
Syntax of Excel HLOOKUP formula:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
In words:
=Starting from the left ( look for this value in the first row, of this range, and get the corresponding figure from the column of this row, [performing approx/exact match])
In the above situation we are given with quantity slabs and the applicable rate. If quantity is mentioned in cell B6 then we can use the following formula to determine the appropriate rate automatically:
=HLOOKUP(B6,B2:H3,2,TRUE)
TRUE argument will help us with approximate match and it will work better as we can have arbitrary units and formula will match it with closest lowest value in the range and fetch the value accordingly.
For example if quantity mentioned is 28 then closest lowest is 20 therefore 10% will be fetched.