Dlookup Excel

The DLookup function returns a single field value based on the information specified in criteria. Although criteria is an optional argument, if you don't supply a value for criteria, the DLookup function returns a random value in the domain. If no record satisfies criteria or if domain contains no records, the DLookup function returns a Null. A VLOOKUP is an Excel-function that looks for something in a range of cells. Then it returns that something that’s in the same row as the value you’re looking for. The only caveat is, that the datasheet has to be listed vertically (which is the case 99% of the time). Now you know what a VLOOKUP is.

  1. Vlookup Excel Formula
  2. Lookup Excel Exact Match

This Excel tutorial explains how to use the Excel LOOKUP function with syntax and examples.

Description

The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array.

The LOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the LOOKUP function can be entered as part of a formula in a cell of a worksheet.

There are 2 different syntaxes for the LOOKUP function:

LOOKUP Function (Syntax #1)

In Syntax #1, the LOOKUP function searches for value in the lookup_range and returns the value in the result_range that is in the same position.

The syntax for the LOOKUP function in Microsoft Excel is:

Parameters or Arguments

value
The value to search for in the lookup_range.
lookup_range
A single row or single column of data that is sorted in ascending order. The LOOKUP function searches for value in this range.
result_range
Optional. It is a single row or single column of data that is the same size as the lookup_range. The LOOKUP function searches for the value in the lookup_range and returns the value from the same position in the result_range. If this parameter is omitted, it will return the first column of data.

Returns

The LOOKUP function returns any datatype such as a string, numeric, date, etc.
If the LOOKUP function can not find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value.
If the value is smaller than all of the values in the lookup_range, then the LOOKUP function will return #N/A.
If the values in the LOOKUP_range are not sorted in ascending order, the LOOKUP function will return the incorrect value.

Applies To

  • Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
Excel vlookup example

Example (as Worksheet Function)

Let's look at some Excel LOOKUP function examples and explore how to use the LOOKUP function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following LOOKUP examples would return:

LOOKUP Function (Syntax #2)

In Syntax #2, the LOOKUP function searches for the value in the first row or column of the array and returns the corresponding value in the last row or column of the array.

The syntax for the LOOKUP function in Microsoft Excel is:

Parameters or Arguments

value
The value to search for in the array. The values must be in ascending order.
array
An array of values that contains both the values to search for and return.

Returns

The LOOKUP function returns any datatype such as a string, numeric, date, etc.
If the LOOKUP function can not find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value.
If the value is smaller than all of the values in the lookup_range, then the LOOKUP function will return #N/A.
If the values in the array are not sorted in ascending order, the LOOKUP function will return the incorrect value.

Applies To

  • Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Example (as Worksheet Function)

Let's look at some Excel LOOKUP function examples and explore how to use the LOOKUP function as a worksheet function in Microsoft Excel:

Frequently Asked Questions

Question: In Microsoft Excel, I have a table of data in cells A2:D5. I've tried to create a simple LOOKUP to find CB2 in the data, but it always returns 0. What am I doing wrong?

Answer: Using the LOOKUP function can sometimes be a bit tricky so let's look at an example. Below we have a spreadsheet with the data that you described.

In cell F1, we've placed the following formula:

And yes, even though CB2 exists in the data, the LOOKUP function returns 0.

Dlookup Excel

Now, let's explain what is happening. At first, it looks like the function isn't finding CB2 in the list, but in fact, it is finding something else. Let's fill in the empty cells in D3:D5 to explain better.

If we place the values TEST1, TEST2, TEST3 in cells D3, D4, 5, respectively, we can see that the LOOKUP function is in fact returning the value TEST2. So we ask ourselves, when we are looking up CB2 in the data and CB2 exists in the data, why is it returning the value for CB19? Good question. The LOOKUP function assumes that the data in column A is sorted in ascending order.

If you look closer at column A, it is not in fact sorted in ascending order. If we quickly sorted column A, it would look like this:

Now the LOOKUP function correctly returns 3A when it is looking up CB2 in the data.

To avoid these sorting problems with your data, we recommend using VLOOKUP function in this case. Let's show you how we would do this. If we changed our formula below (but left our data in column A in the original sort order):

The following VLOOKUP formula would return the correct value of 3A.

The VLOOKUP function does not require us to have the data sorted in ascending order since we used FALSE as the last parameter - which means that it is looking for an exact match.

Question: I have the following LOOKUP formula:

I also need to add zero to the lookup vector and result vector. How do I do this?

Answer: Using numbers in Excel can be tricky, as you can enter them either as numeric or text values. Because of this, there are 2 possible solutions.

Numeric Solution

If you have entered your zero as a numeric value, then the following formula will work:

Text Solution

If you have entered your zero as a text value, then the following formula will work:

Question: For the following function in Microsoft Excel:

How do I get it to return a blank cell if the LOOKUP value (M14) is blank?

Answer: To check for a blank value in cell M14, you can use the IF function and ISBLANK function as follows:

Now if the value in cell M14 is blank, the formula will return a blank. Otherwise it will perform the LOOKUP function as before.

In my earlier post, I had written about VLOOKUP in Excel. It was a massive post of around 2500 words, it explains most of the things about the vertical lookup function in excel. Today’s post is an extension to that post and here we will understand how to apply a VLOOKUP in VBA.

If you haven’t read that post then I would strongly recommend you read that post before going any further. [Read Here]

Assuming that you have basic knowledge of the VLOOKUP function we will move further.

Note: To perform these programs yourself, you may need to enable macros in excel. Read this post to know how to do this.

Syntax of VBA VLOOKUP

You can use VLookUp in macros by following any of the below ways:

Or

Note: If you are searching for something similar to the VLOOKUP function for Access then probably you should use DLOOKUP.

5 Examples of Using VLOOKUP in VBA

Now let’s move to some practical examples of using VLookUp in VBA codes.

Example 1

Using VLookUp find the monthly salary of “Justin Jones” from the below table. Display the salary using a dialog box.

Below is the code for this:

Explanation: In this code, we have used a variable ‘E_name’ to store the employee name whose salary is to be fetched. After this, we have simply supplied the employee name and other required arguments to the VLOOKUP and it returns the salary of the corresponding Employee.

Example 2

Now make the above program a little customizable by accepting the Employee name from the user. If the user enters any Employee name that is not present in the table then the program should be able to convey this clearly to the user.

To accomplish this we can use the below code:

Explanation: In this code, we are accepting the user input using an InputBox function. If the Employee name entered by the user is found, then VLookUp returns its corresponding salary. However, if the employee name is not present in the table then VLOOKUP throws a “1004 Error”.

And, we have created an error handler to catch such cases for conveying the user that entered employee name doesn’t exist.

Example 3

In this example we will try to write a code that adds the Department field from the Employee Table 1 to our old Employee Table.

As you can see that in both these tables there is only one common column i.e. Employee_ID. So, in this case, we will have to apply the VLookUp based on the Employee ID.

Below is the code to do this:

Product description Innovative Grey Matters brushes reduce any distraction a painter may have with light or color mixing. The bristles, ferrule and handle are all non-reflective and the neural grey bristles minimize color distortion. This set of 6 Short-handle Synthetic Watercolor Brushes contains shapes and sizes designed to work together. Purdy 3-Pack XL Nylon- Polyester Blend Angle Multiple Sizes Paint Brush Set. The Purdy XL brushes feature the iconic copper ferrule. Purdy loads the brush with just the right blend of Dupont solid, round and tapered Tynex and orel filaments for brushes that perform no matter what the job. Note: We typically add false inflation alerts on any items that have jumped 300% or more in price compared to our current listed price. We typically only do this for items costing 100,000 NP, or buyable items that have gone unbuyable. For example, an item that. Grey Paint Brush $ 3.99. Rated 5.00 out of 5 based on 5 customer ratings. Aww this paint brush is all sad and droopy. Grey Paint Brush quantity. Add to Wishlist Add to Compare. Category: Paint Brushes Tags: r101, Special. Share: Description Reviews (5). Grey paint brush stroke.

Explanation: This code takes each ‘lookup_value’ from the Employee ID field (one at a time), looks up its corresponding Department, and then populates the corresponding department value at the appropriate place.

Please note that in this code we have just pasted the result of the VLOOKUP formula, and not the VLookUp formula itself (Refer Example 5).

Example 4

In this example we will try to write a code that displays all the details of an employee from the Employee table (as shown below) when its Employee ID is entered.

Example

Below is the code that can accomplish this:

Explanation: In this example, we have asked the user to enter the Employee Id and then we have used multiple VLookUp Statements and concatenated their outputs to show all the details in a single message box.

Example 5

Redo example 3 but this time paste the whole VLookUp formula instead of pasting only the result.

Below is the code for doing this:

Explanation: This code is very similar to the one that we have discussed in Example 3, the only difference between these formulas is that here we are copying the VLookUp formula directly in the cells.

In this code, we have applied the VLOOKUP in R1C1 form. So, the formula =VLOOKUP(RC[-4], R3C8:R13C9, 2, False) means =VLOOKUP(<4 cells to the left of current cell>, <Range of Employee Table 1>, <column to be fetched>, <exact match>).

Vlookup Excel Formula

One thing that is worth noting here is: the square brackets ( [ ] ) in your R1C1 formula indicate that you are specifying a relative range. If you want to specify an absolute range, you need to specify the R1C1 cells without brackets; e.g. R3C8:R13C9.

Lookup Excel Exact Match

So, this was all about VBA VLOOKUP .