Lookup in excel with example

※ Download: Lookup in excel with example How do you fix it? Look up a value of the last non-blank cell in a column If you have a column of dynamically populated data, you may want to pick the most recently added entry, i. In some cases, you may need to move the columns around so the first column contains the correct data. I have a given quantity for water consumed in kl eg 50kl per month. It finds a match in cell A2, as it contains ABC in ABC Ltd. Excel Vlookup Tutorial Part 3 - Vlookup Example With Exact Match - First Match If the leftmost column of the table contains duplicates, the VLOOKUP function matches the first instance. The tutorial explains the vector and array forms of the Excel LOOKUP function and demonstrates typical and non-trivial uses of LOOKUP in Excel with formula examples. Of course, there can be many variations of the basic scenario: you may be looking for the closest match rather than exact match, you may want to search vertically in a column or horizontally in a row, evaluate one or multiple criteria, etc. However, the essence is the same - you need to know how to look up in Excel. Microsoft Excel provides a handful of different ways to do lookup. To begin with, let's learn a function that is designed to handle the simplest cases of vertical and horizontal lookup. As you can easily guess, I am talking about the LOOKUP function. There are two forms of LOOKUP in Excel: Vector and Array. Each form is explained individually below. Excel LOOKUP function - vector form In this context, a vector refers to a one-column or one-row range. Consequently, you use the vector form of LOOKUP to search one row or one column of data for a specified value, and pull a value from the same position in another row or column. It can be a number, text, logical value of TRUE or FALSE, or a reference to a cell containing the lookup value. It must be sorted in ascending order. The following examples demonstrate two simple Lookup formulas in action. Vertical Lookup formula - search in one-column range Let's say, you have a list of sellers in column D D2:D5 and the products they sold in column E E2:E5. You are creating a dashboard where your users will enter the seller's name in B2 and you need a formula that would pull a corresponding product in B3. In the meantime, please remember the following simple facts that will help you bypass possible pitfalls and prevent common errors. If you need to do lookup on unsorted data, then use either or. More precisely, a Lookup formula searches for exact match first. If it cannot find the lookup value exactly, it looks up the next smallest value, i. Excel LOOKUP function - array form The array form of the LOOKUP function searches the specified value in the first column or row of the array and retrieves a value from the same position in the last column or row of the array. The values in the first column or row of the array depending on whether you do V-lookup or H-lookup must be sorted in ascending order. Uppercase and lowercase characters are deemed equivalent. The array form of the Excel LOOKUP function should not be confused with. Although it operates on arrays, LOOKUP is still a regular formula, which is completed in the usual way by pressing the Enter key. The functionality of the Excel LOOKUP array form is limited and we don't recommend using it. Instead, you can use the or function, which are the improved versions to do vertical and horizontal lookup, respectively. How to use LOOKUP function in Excel - formula examples Although there exist more powerful functions to look up and match in Excel which is the subject of our next tutorial , LOOKUP comes in handy in many situations, and the following examples demonstrate a couple of non-trivial uses. Please note, all of the below formulas use the of Excel LOOKUP. Look up a value of the last non-blank cell in a column If you have a column of dynamically populated data, you may want to pick the most recently added entry, i. So, to retrieve the last value in a specific column, you just need to supply the corresponding column reference. In the above example, the formula in F2 returns this array: {TRUE;TRUE;TRUE;TRUE;FALSE... The key to understanding the logic is that Excel LOOKUP searches with approximate match, i. If you wish to get the number of the row holding the last value, then use the ROW function to retrieve it. For instance, it can be used to get a value associated with the last instance of a specific value in a row. This may sound a bit obscure, but the following example will make things easier to understand. Assuming you have a summary table where column A contains the seller names and subsequent columns contain data of some kind for each month. However, the syntax of the Excel LOOKUP function allows supplying the vectors in the form of a , which enables you to replicate the functionality of with a more compact and easy-to-read formula. If you are using Excel 2016 as part of an Office 365 subscription, you can use the for similar purposes. I hope these examples have shed some light on how the LOOKUP function works. To better understand the formulas, you are welcome to download these. In the next tutorial, we will discuss a few other ways to do lookup in Excel and explain which Lookup formula is best to be used in which situation. I thank you for reading and hope to see you on our blog next week! Now the reference to the range is locked and you can move on to the next step by typing a comma. Example 6 — Partial Lookup using Wildcard Characters and VLOOKUP can be really helpful in many situations. How to use LOOKUP function in Excel - formula examples Although there exist more powerful functions to look up and match lookup in excel with example Excel which is the subject of our next tutorialLOOKUP comes in handy in many situations, and the following examples demonstrate a couple of non-trivial uses. Similarly, you can create one in H3 for the subjects. For example, if you want to search columns A and B, down to row 6. VLOOKUP is one of the most well-known Excel functions — and not without reason. I earn a small commission if you buy any products using my affiliate links to Amazon. Description The Microsoft Excel LOOKUP function returns a value from a range one row or one column or from an array. You know something in Excel if you know how to use the VLOOKUP function. The value 2 third argument tells the VLOOKUP function to return the value in the same row from the second column of the red table.