Today I would like to introduce you to the LOOKUP function, which has many relevant knowledge points. Beginners will have some difficulty in learning the LOOKUP function, and the video will introduce the common use of this function and the notes in the process of using it.

Take this spreadsheet as an example. If we want to find out who is the purchaser based on the lump sum in cell J9, we can use the LOOKUP function. The general statement structure of the LOOKUP function is LOOKUP(Lookup_value,Lookup_vector, Result_vector).

Place the cursor to cell K9 , click the Formulas tab, and click the Insert Function button. In the Search for a function edit box, type LOOKUP to find the LOOKUP function.

• Lookup_value is the value that we need to search. In the lookup value, choose cellK9.

• Lookup_vector is a range that we need to find. Select F5:F21 cell range in the lookup range.

• Result_vector is a range of our returned value. Select the G5:G21 cell range in the returned value range, and click OK to get the desired result.

Place the cursor in the lower right corner of the cell. When the cursor turns into a black cross, drop down the cell, we will find that the returned purchaser's name does not correspond to the lump sum.

Because the lookup range of the LOOKUP function needs to be sorted in ascending order, which is also a feature of the LOOKUP function. By this time, we can select the F5:F21 cell area, click the Sort drop-down button, and select the Ascending option. Then we can find that the purchaser's name and the lump sum correspond to each other.

When we drop down the cell again, we will find that cell K12 does not show the results to be found. What is the reason for this? By now, we need to use absolute references. Place the mouse on the upper function edit box, click the lookup range, and press F4 to add an absolute references. In the same way, use the mouse to click the return value range, then press F4 to add an absolute references, and drop down the cell after completion. ThecellK12 will return the name of the purchaser we are looking up. If you want to learn more about absolute references, you're welcome to enter WPS Academy.

Unlike the VLOOKUP function, the LOOKUP function can also perform a reverse lookup. If we want to find the corresponding purchase item through the purchaser's name, it will be much more convenient to use the LOOKUP function. Before using the LOOKUP function, we need to sort the lookup range in ascending order.

1. Select the G5:G21 cell range, click the Home tab and the Sort drop-down button, choose the Ascending option.

2. Click the Formulas tab, click the Insert Function button, enter LOOKUP in the Search for a function edit box to find the LOOKUP function.

3. Select cell K21 at the lookup value, select G5:G21 cell range plus absolute references at the lookup range, select C5:C21 cell range plus absolute references at the returned value, and click OK to return to the required purchase item name.

4. Then we can double-click the filling formula to get all the corresponding purchase item names.

At this point, someone may have a question. If we apply an ascending sort without a lookup range, can we find the correct result? In fact, the answer is yes. But, we have to use a fixed lookup formula. We will launch some awesomevideos at WPS Academy later. To be office excel advancers, we could learn how to use WPS Office Spreadsheet online in WPS Academy. 

All above is the common use and notice of the LOOKUP function. Did you get it?