Using functions skillfully: Making data tables is so simple

Number of lookup--and determinant ratio

What is the job responsibility of lookup? A number is compared with one row or column of data, and the corresponding value in the other group of data is extracted after the matching value is found.

Wage scale: by numerical comparison

It is a common application to calculate different tax rates according to different wages. Let's take a look at this "Payroll Tax Enquiry" form (see Figure 1). Now, on the right, according to "Income" (column f), get the corresponding "tax rate" (column g) directly. In the calculation of the 1th "tax rate", the input function formula "=lookup (F4, $B: $B, $D: $D)", enter, you can get "36%".

Figure 1 Numerical comparison

How did this result come from? With the 1th income in F4 "3,409", in contrast to the "lowest revenue" data ("$B: $B") on the left-hand table, although "3,409" does not match exactly the same data in the "lowest revenue" file, it matches the maximum number of "501" that is smaller than it. Thus, the corresponding "36%" on the same line is extracted.

Book sales table: Compare with text

The lookup function can also be compared to text. In this book sales enquiry form (see Figure 2), use the "number" (A15 cell) text entered in the following table as the number of queries, in contrast to the number column ($A: $A) of the table above, the data corresponding to the "Textbook Name" column ($B: $B) is extracted after the matching text is queried. The formula is "=lookup (A15, $A: $A, $B: $B)".

Figure 2 Text comparison

vlookup--number and table ratio

Lookup has a big Brother--vlookup function. The two brothers have a lot in common, but Big Brother is more capable. VLOOKUP contrasts with a "table" rather than a 1 or 1 row of the lookup function, and VLOOKUP can choose to use either an exact query or a fuzzy query, whereas lookup has only a fuzzy query.