The following formula searches column A for each of the serial numbers listed in column C.
It then analyses the corresponding number from column B: for numbers less than or equal to 40, 10 is added; and for numbers greater than or equal to 90, 10 is subtracted. Other numbers are returned as is.
As can be seen, the underlined VLOOKUP part of the formula is repeated a number of times, thus creating a long and very complicated formula.
To make our spreadsheet easier to understand, we would like to simplify the formula.
Step1: Define a name for the repeated part of the formula:
Select a cell > Insert > Name > Define > type the name “value” in the Names in workbooks text box > and enter the following formula in the Refers to box:
Step 2: Enter the following formula in cell D2, and copy it down the column:
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.