In the range B12:E12, Benicio wants to display a rating depending on the total sales for each quarter. He listed the rating criteria in the range A14:F15. For example, if total sales in Quarter 1 are between $5900 and $5999, the Performance rating is Good. Enter the performance ratings as follows:
a. In cell B12, start to enter a formula using the HLOOKUP function.
b. Use the Total Q1 sales (cell B8) as the value to look up.
c. Use the Revenue Amts and Rating information (range $B
d. Specify that row 2 contains the value you want to return, which is the performance rating.
e. Specify an approximate match (TRUE) because the Revenue Amts represent ranges of values.
f. Fill the range C12:E12 with the formula in cell B12 to enter ratings for Quarters 2–4.
Reading books is a great way to learn. Here are some of the books we recommend.
a-e. The formula is
=HLOOKUP(b8,$b$14, $F$15, 2, TRUE)f. Move the cursor in the bottom of B12 cell, '+' sign will appear. Drag it to up to E12 cell.
Hope it helped!
Here is video explanation by Taandar: