In the example shown, we want to look up employee departments and groups using VLOOKUP by matching the first and last name of an employee.
One limitation of VLOOKUP is that it only handles one condition: the lookup_value, which is matched against the first column in the table....Read more
In this example, the goal is to perform a two-way lookup based on the name in cell H4 and the month in cell H5 with the VLOOKUP function. Inside the VLOOKUP function, the column index argument is normally hard-coded as a static number. However, you can create a dynamic column index...Read more
The SUMPRODUCT function multiplies arrays together and returns the sum of products. If only one array is supplied, SUMPRODUCT will simply sum the items in the array. For example, if we give SUMPRODUCT one array...Read more
In this example, the goal is to configure VLOOKUP to perform a lookup in a table where the first column contains numbers entered as text, and the lookup value is a true number. This mismatch between numbers and text will cause VLOOKUP to return an #N/A error.
Typically, the lookup column...Read more
Although VLOOKUP is a pretty simple function, there is plenty that can go wrong. Quickly learn more than 20 things you should know about VLOOKUP to make your experience pleasant and profitable.Read more
Many users aren't aware of it, but VLOOKUP will use approximate match mode by default. This can be a disaster because VLOOKUP can return a totally incorrect result. Read below to learn how match modes work in VLOOKUP, and how to avoid this dangerous problem....Read more
In this example, the goal is to sum hours per project across three different worksheets: Sheet1, Sheet2, and Sheet3. The data on each of the three sheets has the same structure as Sheet1, as seen below:
...Read more
In this example, the goal is to "reverse" the items in the range B5:B14, so that the first item appears last, the second item appears second to last, and so on. When you first encounter a problem like this in Excel, your first instinct might be to sort the values in some way using the...Read more
In this example, the goal is to retrieve employee information from a table using only a partial match on the last name. In other words, by typing "Aya" into cell H4, the formula should retrieve information about Michael Ayala.
The VLOOKUP function...Read more
