The difference between lookup function and VLOOKUP function in Excel

What are the differences between lookup and VLOOKUP in Excel query functions? How should they be grasped in the application? Please see this article to explain.

Number of ★lookup--and determinant ratio

What is the job responsibility of lookup? Compare a number with one row or column of data, and find the matching value, and then extract the corresponding value from the other set of data.

• Salary 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 calculating the 1th "tax rate", enter the function formula "=lookup (F4, $B $: $B $, $D $: $D $)", return, and get "36%".

How did this result come from? The 1th revenue number "$123,409" in F4 is compared with the "lowest income" data ("$B $ $B $") on the left-hand table, although "$123,409" does not match exactly the same data in the "lowest income" file, But it will match the maximum number of "$58,501" which 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 A15 cell text entered in the following table as the number of queries, in contrast to the number column ($A $: $A $11) of the previous table, and after you have queried the matching text, list the "Textbook Name" column ($B $: $B $11) The corresponding data is extracted. The formula is "=lookup (A15, $A $: $A $11, $B $: $B $11)".

★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.

• Fuzzy Matching

When using the VLOOKUP function for fuzzy query, it is almost identical with the function of lookup. We use the VLOOKUP function to extract the result of the payroll tax in the 1th example. The function formula is "=vlookup (F4, $B $: $D $8,3,true)".

In this function, use the 1th revenue "$123,409" (F4 cell) as the number of comparisons, compare it to the 1th column of the table on the left ($B $: $D $), although "$123,409" does not match exactly the same data in the "lowest revenue" file, but the function The last parameter is "Ture" ("Ture" is a fuzzy query), so it matches the maximum number of "$58,501" which is smaller than it. The data in the 3rd column of the table (the 3rd parameter of the function is "3") is extracted and the result is also "36%".