Bạn đang xem bài viết How To Use The Excel Vlookup Function được cập nhật mới nhất tháng 2 năm 2024 trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
VLOOKUP is an Excel function to get data from a table organized vertically. Lookup values must appear in the first column of the table passed into VLOOKUP. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches.
V is for verticalThe purpose of VLOOKUP is to get information from a table organized like this:
Using the Order number in column B as a lookup value, VLOOKUP can get the Customer ID, Amount, Name, and State for any order. For example, to get the customer name for order 1004, the formula is:
=
VLOOKUP
(
1004
,
B5:F9,
4
,
FALSE
)
// returns "Sue Martin"
For horizontal data, you can use the HLOOKUP, INDEX and MATCH, or XLOOKUP.
VLOOKUP is based on column numbersWhen you use VLOOKUP, imagine that every column in the table is numbered, starting from the left. To get a value from a particular column, provide the appropriate number as the “column index”. For example, the column index to retrieve the first name below is 2:
The last name and email can be retrieved with columns 3 and 4:
=
VLOOKUP
(
H3,
B4:E13,
2
,
FALSE
)
// first name
=
VLOOKUP
(
H3,
B4:E13,
3
,
FALSE
)
// last name
=
VLOOKUP
(
H3,
B4:E13,
4
,
FALSE
)
// email address
VLOOKUP only looks rightVLOOKUP can only look to the right. The data you want to retrieve (result values) can appear in any column to the right of the lookup values:
If you need to lookup values to the left, see INDEX and MATCH, or XLOOKUP.
Exact and approximate matchingVLOOKUP has two modes of matching, exact and approximate. The name of the argument that controls matching is “range_lookup“. This is a confusing name, because it seems to have something to do with cell ranges like A1:A10. Actually, the word “range” in this case refers to “range of values” – when range_lookup is TRUE, VLOOKUP will match a range of values rather than an exact value. A good example of this is using VLOOKUP to calculate grades.
It is important to understand that range_lookup defaults to TRUE, which means VLOOKUP will use approximate matching by default, which can be dangerous. Set range_lookup to FALSE to force exact matching:
=
VLOOKUP
(
value,
table,
col_index)
// approximate match (default)
=
VLOOKUP
(
value,
table,
col_index,
TRUE
)
// approximate match
=
VLOOKUP
(
value,
table,
col_index,
FALSE
)
// exact match
Note: You can also supply zero (0) instead of FALSE for an exact match.
Exact matchIn most cases, you’ll probably want to use VLOOKUP in exact match mode. This makes sense when you have a unique key to use as a lookup value, for example, the movie title in this data:
The formula in H6 to find Year, based on an exact match of movie title, is:
=
VLOOKUP
(
H4,
B5:E9,
2
,
FALSE
)
// FALSE = exact match
Approximate matchIn cases when you want the best match, not necessarily an exact match, you’ll want to use approximate mode. For example, below we want to look up a commission rate in the table G5:H10. The lookup values come from column C. In this example, we need to use VLOOKUP in approximate match mode, because in most cases an exact match will never be found. The VLOOKUP formula in D5 is configured to perform an approximate match by setting the last argument to TRUE:
=
VLOOKUP
(
C5,
$G$5:$H$10,
2
,
TRUE
)
// TRUE = approximate match
VLOOKUP will scan values in column G for the lookup value. If an exact match is found, VLOOKUP will use it. If not, VLOOKUP will “step back” and match the previous row.
Note: data must be sorted in ascending order by lookup value when you use approximate match mode with VLOOKUP.
First match=
VLOOKUP
(
E5,
B5:C11,
2
,
FALSE
)
// returns 17
Wildcard matchThe VLOOKUP function supports wildcards, which makes it possible to perform a partial match on a lookup value. For instance, you can use VLOOKUP to retrieve values from a table after typing in only part of a lookup value. To use wildcards with VLOOKUP, you must specify the exact match mode by providing FALSE or 0 for the last argument, range_lookup. The formula in H7 retrieves the first name, “Michael”, after typing “Aya” into cell H4:
=
VLOOKUP
(
$H$4&
"*"
,
$B$5:$E$104,
2
,
FALSE
)
Read a more detailed explanation here.
Twoway lookupInside the VLOOKUP function, the column index argument is normally hardcoded as a static number. However, you can also create a dynamic column index by using the MATCH function to locate the right column. This technique allows you to create a dynamic twoway lookup, matching on both rows and columns. In the screen below, VLOOKUP is configured to perform a lookup based on Name and Month. The formula in H6 is:
=
VLOOKUP
(
H4,
B5:E13,
MATCH
(
H5,
B4:E4,
0
),
0
)
For more details, see this example.
Note: In general, INDEX and MATCH is a more flexible way to perform twoway lookups.
Multiple criteriaThe VLOOKUP function does not handle multiple criteria natively. However, you can use a helper column to join multiple fields together, and use these fields like multiple criteria inside VLOOKUP. In the example below, Column B is a helper column that concatenates first and last names together with this formula:
=
C5&
D5// helper column
VLOOKUP is configured to do the same thing to create a lookup value. The formula in H6 is:
=
VLOOKUP
(
H4&
H5,
B5:E13,
4
,
0
)
For details, see this example.
Note: INDEX and MATCH and XLOOKUP are more robust ways to handle lookups based on multiple criteria.
VLOOKUP and #N/A errorsIf you use VLOOKUP you will inevitably run into the #N/A error. The #N/A error just means “not found”. For example, in the screen below, the lookup value ”Toy Story 2″ does not exist in the lookup table, and all three VLOOKUP formulas return #N/A:
One way to “trap” the NA error is to use the IFNA function like this:
The formula in H6 is:
=
IFNA
(
VLOOKUP
(
H4,
B5:E9,
2
,
FALSE
),
"Not found"
)
The message can be customized as desired. To return nothing (i.e. to display a blank result) when VLOOKUP returns #N/A you can use an empty string like this:
=
IFNA
(
VLOOKUP
(
H4,
B5:E9,
2
,
FALSE
),
""
)
// no message
The #N/A error is useful because it tells you something is wrong. In practice, there are many reasons why you might see this error, including:
The lookup value does not exist in the table
The lookup value is misspelled, or contains extra space
Match mode is exact, but should be approximate
The table range is not entered correctly
You are copying VLOOKUP, and the table reference is not locked
Read more: VLOOKUP without #N/A errors
More about VLOOKUP Other notes
Range_lookup controls whether value needs to match exactly or not. The default is TRUE = allow nonexact match.
Set range_lookup to FALSE to require an exact match and TRUE to allow a nonexact match.
If range_lookup is TRUE (the default setting), a nonexact match will cause the VLOOKUP function to match the nearest value in the table that is still less than value.
When range_lookup is omitted, the VLOOKUP function will allow a nonexact match, but it will use an exact match if one exists.
If range_lookup is TRUE (the default setting) make sure that lookup values in the first row of the table are sorted in ascending order. Otherwise, VLOOKUP may return an incorrect or unexpected value.
If range_lookup is FALSE (require exact match), values in the first column of table do not need to be sorted.
How To Use The Excel Counta Function
Random list of names
At the core, this formula uses the INDEX function to retrieve 10 random names from a named range called “names” which contains 100 names. For example, to retrieve the fifth name from the list, we use INDEX like this…
Add row numbers and skip blanks
In the example shown, the goal is to add row numbers in column B only when there is a value in column C. The formula in B5 is:
=IF(ISBLANK(C5),””,COUNTA($C$5:C5))
The IF function first checks if cell C5 has…
Cell contains all of many things
The key is this snippet:
ISNUMBER(SEARCH(things,B5)
This is based on another formula (explained in detail here) that simply checks a cell for a single substring. If the cell contains the substring, the formula…
Count cells that are blank
The COUNTBLANK function counts the number of cells in the range that don’t contain any value and returns this number as the result. Cells that contain text, numbers, dates, errors, etc. are not counted. COUNTBLANK is…
Last row in mixed data with no blanks
This formula uses the COUNTA function to count values in a range. COUNTA counts both numbers and text to so works well with mixed data.
The range B4:B8 contains 5 values, so COUNTA returns 5. The number 5 corresponds…
Count unique values
This example uses the UNIQUE function to extract unique values. When UNIQUE is provided with the range B5:B16, which contains 12 values, it returns the 7 unique values seen in D5:D11. These are returned directly to the…
Dynamic named range with OFFSET
This formula uses the OFFSET function to generate a range that expands and contracts by adjusting height and width based on a count of nonempty cells.
The first argument in OFFSET represents the first cell in the…
Running count group by n size
The core of this formula is the COUNTA function, configured with an expanding range like this:
COUNTA($B$5:B5)
As the formula is copied down the column, the range starting with B5 expands to include each new row, and…
Count cells not equal to many things
First, a little context. Normally, if you have just a couple things you don’t want to count, you can use COUNTIFS like this:
But this doesn…
Project complete percentage
In this example if a task is marked “Done”, then it is considered complete. The goal is to calculate the percent complete for the project by showing the ratio of complete tasks to total tasks, expressed as a percentage…
Count sold and remaining
The COUNTA function counts nonblank cells that contain numbers or text. The first COUNTA counts nonblank cells in the range B5:B11 and returns the number 7:
COUNTA(B5:B11)
The second COUNTA function…
Generate random text strings
The new dynamic array formulas in Excel 365 make it much easier to solve certain tricky problems with formulas.
In this example, the goal is to generate a list of random 6character codes. The randomness is handled by…
Sort by random
The SORTBY function allows sorting based on one or more “sort by” arrays, as long long as they have dimensions that are compatible with the data being sorted. In this example, there are 10 values being sorted, the…
Score quiz answers with key
This formula uses the named range “key” (C4:G4) for convenience only. Without the named range, you’ll want to use an absolute reference so the formula can be copied.
In cell I7, we have this formula:
=SUM(–(C7:G7=…
Reverse a list or range
The heart of this formula is the INDEX function, which is given the list as the array argument:
=INDEX(list
The second part of the formula is an expression that works out the correct row number as the formula is…
How To Use The Excel Countifs Function
The COUNTIFS function in Excel counts the number of cells in a range that match one supplied criteria. Unlike the older COUNTIF function, COUNTIFS can apply more more than one condition at the same time. Conditions are supplied with range/criteria pairs, and only the first pair is required. For each additional condition, you must supply another range/criteria pair. Up to 127 range/criteria pairs are allowed.
COUNTIFS is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and COUNTIFS requires a cell range for range arguments, you can’t use an array.
Basic exampleWith the example shown, COUNTIFS can be used to count records using 2 criteria as follows:
=
COUNTIFS
(
C5:C14,
"red"
,
D5:D14,
"tx"
)
// red and TX
Notice the COUNTIFS function is not casesensitive.
Double quotes ("") in criteriaIn general, text values need to be enclosed in double quotes, and numbers do not. However, when a logical operator is included with a number, the number and operator must be enclosed in quotes as shown below:
=
COUNTIFS
(
A1:A10,
100
)
// count equal to 100
=
COUNTIFS
(
A1:A10,
"jim"
)
// count equal to "jim"
Note: showing one condition only for simplicity. Additional conditions must follow the same rules.
Value from another cellWhen using a value from another cell in a condition, the cell reference must be concatenated to an operator when used. In the example below, COUNTIFS will count the values in A1:A10 that are less than the value in cell B1. Notice the less than operator (which is text) is enclosed in quotes, but the cell reference is not:
=
COUNTIFS
(
A1:A10,
"<"
&
B1)
// count cells less than B1
Note: COUNTIFS is one of several functions that split conditions into two parts: range + criteria. This causes some inconsistencies with respect to other formulas and functions.
Not equal toTo construct "not equal to" criteria, use the "" operator surrounded by double quotes (""). For example, the formula below will count cells not equal to "red" in the range A1:A10:
Blank cellsCOUNTIFS can count cells that are blank or not blank. The formulas below count blank and not blank cells in the range A1:A10:
=
COUNTIFS
(
A1:A10,
""
)
// blank
DatesThe easiest way to use COUNTIFS with dates is to refer to a valid date in another cell with a cell reference. For example, to count cells in A1:A10 that contain a date greater than a date in B1, you can use a formula like this:
The safest way hardcode a date into COUNTIFS is with the DATE function. This guarantees Excel will understand the date. To count cells in A1:A10 that contain a date less than September 1, 2024, you can use:
=
COUNTIFS
(
A1:A10,
"<"
&
DATE
(
2024
,
9
,
1
))
// dates less than 1Sep2024
WildcardsThe wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in criteria. A question mark (?) matches any one character, and an asterisk (*) matches zero or more characters of any kind. For example, to count cells in a A1:A5 that contain the text "apple" anywhere, you can use a formula like this:
=
COUNTIFS
(
A1:A5,
"*apple*"
)
// count cells that contain "apple"
The tilde (~) is an escape character to allow you to find literal wildcards. For example, to count a literal question mark (?), asterisk(*), or tilde (~), add a tilde in front of the wildcard (i.e. ~?, ~*, ~~).
Notes
Multiple conditions are applied with AND logic, i.e. condition 1 AND condition 2, etc.
Each additional range must have the same number of rows and columns as range1, but ranges do not need to be adjacent. If you supply ranges that don't match, you'll get a #VALUE error.
The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters.
To find a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).
How To Use The Excel Roundup Function
The ROUNDUP function works like the ROUND function, except the ROUNDUP function will always round numbers up. The number of places to round to is controlled by the num_digits argument. Positive numbers round to the right of the decimal point, negative numbers round to the left, and zero rounds to the nearest 1. The table below summarizes this behavior:
Digits Behavior
Round up to nearest .1, .01, .001, etc.
Round up to nearest 10, 100, 1000, etc.
=0 Round up to nearest 1
Example #1 – round to rightTo round up values to the right of the decimal point, use a positive number for digits:
=
ROUNDUP
(
A1,
1
)
// Round up to 1 decimal place
=
ROUNDUP
(
A1,
2
)
// Round up to 2 decimal places
=
ROUNDUP
(
A1,
3
)
// Round up to 3 decimal places
=
ROUNDUP
(
A1,
4
)
// Round up to 4 decimal places
Example #2 – round to leftTo round up values to the left of the decimal point, use zero or a negative number for digits:
=
ROUNDUP
(
A1,
0
)
// Round up to nearest whole number
=
ROUNDUP
(
A1,

1
)
// Round up to nearest 10
=
ROUNDUP
(
A1,

2
)
// Round up to nearest 100
=
ROUNDUP
(
A1,

3
)
// Round up to nearest 1000
=
ROUNDUP
(
A1,

4
)
// Round up to nearest 10000
Example #3 – nestingOther operations and functions can be nested inside the ROUNDUP function. For example, to round the result of A1 divided by B1, you can use a formula like this:
=
ROUNDUP
(
A1/
B1,
0
)
// round up result to nearest whole number
Rounding functions in Excel
To round normally, use the ROUND function.
To round to the nearest multiple, use the MROUND function.
To round down to the nearest specified place, use the ROUNDDOWN function.
To round down to the nearest specified multiple, use the FLOOR function.
To round up to the nearest specified place, use the ROUNDUP function.
To round up to the nearest specified multiple, use the CEILING function.
To round down and return an integer only, use the INT function.
To truncate decimal places, use the TRUNC function.
How To Use The Excel Countif Function
COUNTIF is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and COUNTIF requires a cell range, you can’t use an array.
COUNTIF only supports a single condition. If you need to apply multiple criteria, use the COUNTIFS function. If you need to manipulate values in the range argument as part of a logical test, see the SUMPRODUCT and/or FILTER functions.
Basic exampleIn the worksheet shown above, the following formulas are used in cells G5, G6, and G7:
=
COUNTIF
(
B5:B12,
"jim"
)
// count name = "jim"
=
COUNTIF
(
C5:C12,
"ca"
)
// count state = "ca"
Notice COUNTIF is not casesensitive, “CA” and “ca” are treated the same.
Double quotes (“”) in criteriaIn general, text values need to be enclosed in double quotes (“”), and numbers do not. However, when a logical operator is included with a number, the number and operator must be enclosed in quotes, as seen in the second example below:
=
COUNTIF
(
A1:A10,
100
)
// count cells equal to 100
=
COUNTIF
(
A1:A10,
"jim"
)
// count cells equal to "jim"
Value from another cellA value from another cell can be included in criteria using concatenation. In the example below, COUNTIF will return the count of values in A1:A10 that are less than the value in cell B1. Notice the less than operator (which is text) is enclosed in quotes.
=
COUNTIF
(
A1:A10,
"<"
&
B1)
// count cells less than B1
Not equal toTo construct “not equal to” criteria, use the “” operator surrounded by double quotes (“”). For example, the formula below will count cells not equal to “red” in the range A1:A10:
Blank cellsCOUNTIF can count cells that are blank or not blank. The formulas below count blank and not blank cells in the range A1:A10:
=
COUNTIF
(
A1:A10,
""
)
// blank
DatesThe easiest way to use COUNTIF with dates is to refer to a valid date in another cell with a cell reference. For example, to count cells in A1:A10 that contain a date greater than the date in B1, you can use a formula like this:
The safest way hardcode a date into COUNTIF is to use the DATE function. This ensures Excel will understand the date. To count cells in A1:A10 that contain a date less than April 1, 2024, you can use a formula like this
=
COUNTIF
(
A1:A10,
"<"
&
DATE
(
2024
,
4
,
1
))
// dates less than 1Apr2024
WildcardsThe wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in criteria. A question mark (?) matches any one character and an asterisk (*) matches zero or more characters of any kind. For example, to count cells in a A1:A5 that contain the text "apple" anywhere, you can use a formula like this:
=
COUNTIF
(
A1:A5,
"*apple*"
)
// cells that contain "apple"
To count cells in A1:A5 that contain any 3 text characters, you can use:
=
COUNTIF
(
A1:A5,
"???"
)
// cells that contain any 3 characters
The tilde (~) is an escape character to match literal wildcards. For example, to count a literal question mark (?), asterisk(*), or tilde (~), add a tilde in front of the wildcard (i.e. ~?, ~*, ~~).
Notes
COUNTIF is not casesensitive. Use the EXACT function for casesensitive counts.
COUNTIF only supports one condition. Use the COUNTIFS function for multiple criteria.
Cell references in criteria are not enclosed in quotes, i.e. "
The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters (zero or more).
To match a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).
COUNTIF requires a range, you can't substitute an array.
COUNTIF returns incorrect results when used to match strings longer than 255 characters.
COUNTIF will return a #VALUE error when referencing another workbook that is closed.
How To Use The Excel Sumif Function
SUMIF is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and SUMIF requires a cell range for the range argument, you can’t use an array.
SUMIF only supports a single condition. If you need to apply multiple criteria, use the SUMIFS function. If you need to manipulate values that appear in the range argument (i.e. extract the year from dates to use in criteria) see the SUMPRODUCT and/or FILTER functions.
Videos
Basic usageIn the worksheet shown, there are three SUMIF examples. In the first example (G6), SUMIF is configured to sum values greater than 100. In the second example (G7), SUMIF returns the sum of values where the color is “red”. In the last example (G8), SUMIF is configured to sum values where the state is “CA” (California).
=
SUMIF
(
B6:B10,
"Jim"
,
D6:D10)
// Rep = Jim
=
SUMIF
(
C6:C10,
"ca"
,
D6:D10)
// State = CA
Notice the equals sign (=) is not required when constructing “is equal to” criteria. Also notice SUMIF is not casesensitive. You can sum values where the Rep is Jim using “jim” or “Jim”.
Criteria in another cell Not equal toTo express "not equal to" criteria, use the "" operator surrounded by double quotes (""):
Again notice SUMIF is not casesensitive.
Blank cellsSUMIF can calculate sums based on cells that are blank or not blank. In the example below, SUMIF is used to sum the amounts in column C depending on whether column D contains "x" or is empty:
=
SUMIF
(
D5:D9,
""
,
C5:C9)
// blank
The best way to use SUMIF with dates is to refer to a valid date in another cell, or use the DATE function. The example below shows both methods:
=
SUMIF
(
B5:B9,
"<"
&
DATE
(
2024
,
3
,
1
),
C5:C9)
WildcardsThe SUMIF function supports wildcards, as seen in the example below:
=
SUMIF
(
B5:B9,
"mi*"
,
C5:C9)
// begins with "mi"
=
SUMIF
(
B5:B9,
"*ota"
,
C5:C9)
// ends with "ota"
=
SUMIF
(
B5:B9,
"????"
,
C5:C9)
// contains 4 characters
See below for more SUMIF formula examples.
Notes
SUMIF only supports one condition. Use the SUMIFS function for multiple criteria.
When sum_range is omitted, the cells in range will be summed.
Cell references in criteria are not enclosed in quotes, i.e. "
The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters (zero or more).
To find a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).
SUMIFS requires a range, you can't substitute an array.
Cập nhật thông tin chi tiết về How To Use The Excel Vlookup Function trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!