How to use the If and Vlookup functions together in Excel Steve Chase. Using Excel VLOOKUP Function with Multiple. Excel VLOOKUP: Basics of VLOOKUP and HLOOKUP explained with examples.
VLOOKUP and Search are two functions that Excel uses to search for text. VLOOKUP finds data in a column and returns the contents of an adjacent cell. Search finds data in a cell and returns the string's position in the larger string. A formula might combine the functions. For example, suppose your inventory spreadsheet uses two types of product code in a string related to the product. The two functions can together find the relevant string and locate it in the code, determining which type of code each product uses.
Identify the Column with Relevant Data Identify the column whose data you want Excel to find in a different array. With this example, this column will list product codes. The different array will list product codes along with strings of data that form the code.
Insert the VLOOKUP Code Insert the following code to the right of the uppermost cell in the column from Step 1: =Search('(=VLOOKUP(A1, F10:I20, 3, FALSE))', A1, 1) Replace the Instances of 'A1' Replace both instances of 'A1' in the formula with the cell reference for the data you want Excel to find in the array. Replace 'F10' with Relevant Data Replace 'F10' with the top cell from the array's first column. Replace 'I10' with Relevant Data Replace 'I10' with the bottom cell from the array's last column. Identify the Column to Search Identify the column in the array that contains the data you want the Search function to find. With this example, suppose that the array's third column contains products' category codes, which are strings that serve as components for the product code. Identify the Column Reference Index Identify the column reference index for the column from Step 6. This value describes the column's position with respect to the first column in the array, which VLOOKUP searches.
If the column is the second column in the array, the column reference number is '2.' If the column is the third column in the array, the column reference number is '3.' Replace '3' with Column Reference Number Replace '3' in the formula with the column reference number you identified in Step 7.
Identify Position for Search Function Identify the position in the string from which you want the Search function to run. If you want Search to look at the entire string, as is most often the case, this position is '1.' If you want Search to ignore the first letter and start searching from the second letter, this position is '2.' Replace '2' with Position from Search Function Replace '2' in the formula with the position from Step 9. Press the Enter Key Press the Enter key. The formula runs, using both functions. With this example, the formula finds a product code in the array.
VLOOKUP finds the inventory code associated with the product. Search finds the inventory code's position in the product code.
This post provides an explanation of how to use the VLOOKUP and MATCH functions to give you better control over how the column index number changes. This is often referred to as a dynamic formula. You will learn how this dynamic duo can help prevent errors and improve your VLOOKUP formulas.
The Dynamic Duo We are going to learn how the MATCH function can be used inside the VLOOKUP function. This helps protect the VLOOKUP from returning errors when changes are made to the workbook. Problems with VLOOKUPs The VLOOKUP is a very useful function, but it doesn't respond to change very well. Have you ever noticed that if you add or delete columns in an area that the VLOOKUP refers to, the result can return an error or incorrect result? This is usually due to the fact that we have specified the column number as a static number in the 3rd argument of the VLOOKUP argument.
The Starbucks Menu Example We can use the to help explain this issue. In that example we wanted to return the price for the size Grande, which was in column 3 of the menu. We put a “3” in the column index argument in the VLOOKUP formula to reference the Grande column (col C). But what if Starbucks decided to add a new size to the menu? Let's say they decide to add a size “Short” to the menu, and put it to the left of the size Tall. In our spreadsheet example, we would need to insert a column after column A for the new size.
This change means that the size Grande is now in column 4 (col D). However, our VLOOKUP still references column 3. Excel does NOT update the formula when a column is inserted or deleted.
Therefore, the formula is now returning the wrong result. This is a problem! But fortunately for us, VLOOKUP has a side-kick named MATCH that will save the day. MATCH to the Rescue We first need to learn how the MATCH function works. The MATCH function is very similar to the VLOOKUP. It's job is to look through a range of cells and find a match. The difference is that it returns a row or column number.
So why the Batman and Robin reference? I like to think of MATCH as VLOOKUP's little brother, or side-kick.
They do very similar jobs, but MATCH packs a smaller punch. Batman is the VLOOKUP and returns a big value in the form of a cell's value. This can be text or a number. Robin is the MATCH and returns a smaller value in the form of a number.
Hopefully this will help you remember and distinguish the difference between the two. The Match Function Components The MATCH function's arguments are similar to the VLOOKUP's.
The following image shows the Excel definition of the VLOOKUP function, and then my simple definition. This simple definition just makes it easier for me to remember the three arguments. I explain this simple definition below as we walk through an example of creating a VLOOKUP formula. The MATCH Example Let's look at the Starbucks menu example again to learn MATCH. In this example we want to use the MATCH function to return the column number for the size Grande. I'll explain why later, but for now we just want to answer the question: “What is the column number for the size Grande?” We will answer this question using the MATCH function.
You can download the file to follow along. Lookupvalue – This is the what argument. In the first argument we tell the VLOOKUP what we are looking for. In this example we are looking for “Grande” in row 1. I have entered the text “Grande” in cell A14, so we can make a reference to cell A14 in the formula. Lookuparray – This is the where argument. Here we need to tell MATCH where to look for the word “Grande”.
I selected the range $A$1:$D$1, which contains the column header names. MATCH will look through row 1 from left-to-right until it finds a match. You can also specify a column for this argument. In that case MATCH would look down the column from top-to-bottom to find a match.
matchtype – The match type tells MATCH how precise to be with the lookup. Here we specify if the function should look for an exact MATCH, or a value that is less than or greater than the lookupvalue. In this example we will use “Exact match”, which is represented by putting a 0 (zero) in the third argument. When your MATCH is looking up text you will generally want to look for an exact match. If you are looking up numbers with the MATCH function then the “Less than” or “Greater than” match types can be very useful. The Result – Grande is in column 3. The MATCH function was able to lookup the word “Grande” in row 1 and return the value of 3 in cell B14.
Now let's see how the MATCH function can be a little more dynamic. In the following screenshot I inserted a column to the left of column B with a new size, “Short”. You will notice that the result of the MATCH formula changed to “4”. This is because the word “Grande” is now in the 4th column of the lookuparray (A1:E1).
You are probably starting to see how this could help our original problem of the VLOOKUP returning the wrong result. VLOOKUP & MATCH – The Dynamic Duo Now let's see how we can combine these two to create a dynamic formula.
We can use the MATCH function inside the VLOOKUP function. Instead of specifying the column number with a static number “3”, we will use the MATCH function in its place. Since the MATCH returns a number, it is a perfect fit for the VLOOKUP's colindexnum argument. The following example illustrates this.
You can follow along in the ‘VLOOKUP & MATCH Example' sheet in the. The original formula looked like the following: =VLOOKUP(A14,$A$2:$E$10, 4,FALSE) In the new formula I replace the “4” with the MATCH formula: =VLOOKUP(A14,$A$2:$E$10, MATCH(B14,$A$1:$E$1,0),FALSE) The MATCH formula references cell B14, which contains the word “Grande”. The formula looks up the word Grande in row 1 and returns a 4 as the result because Grande is in the fourth column of the range A1:E1. The VLOOKUP formula is now much more dynamic with MATCH included. We can add or delete columns to the menu (table), and the VLOOKUP will still return the price for the size that is specified in cell B14.
You could also change either the item in cell A14 or the size in cell B14 to return different prices in cell C14. This makes the formula very flexible, and easier to reuse in other places in the workbook. Conclusion I hope this post has helped you understand how the VLOOKUP and MATCH functions can work together to be a dynamic duo. This team of functions will help prevent errors in your formulas. It will also help you create financial models that are more flexible for data retrieval and scenario analysis. Series: The Lookup Functions Explained This is the 2nd post in a series about the most commonly used lookup functions in Excel.
In the first post I explained the. In this second post I explained how to make lookup formulas more dynamic with the VLOOKUP & MATCH functions. In the third post I explain the. Please leave a comment below with any questions or comments.
Hello, from sunny Costa Rica. Thank you for taking the time to explain in detail the dynamic functions.
It is always nice to learn something new, and Excel is not my strong application although I use it to add, subtract and do small calculations. Same MATCH function can be used to match “Caffe Mocha” down A column, right? The formula as you explained it but referring to A5(Caffe Mocha) and C1 (Grande) is (in Spanish Excel): =BUSCARV($A5,$A$2:$D$10,COINCIDIR(C$1,$A$1:$D$1,0),FALSO) works fine. If I replace $A5 with a MATCH function: BUSCARV(COINCIDIR($A5,$A$1:$A$10,0),$A$2:$D$10,COINCIDIR(C$1,$A$1:$D$1,0),FALSO), doesn’t work. What am I doing wrong?
Hi Jon, Your explanation is very clear, thank you so much. Now that i understand the match and index functions, I am not sure which combination to use for my workbook.
I have a vlookup but I don’t think I am using it correctly and I also want to change it so I’m not so limited. Here is the current formula: =VLOOKUP(A2,DUTYTABLE,2,FALSE).(E2), where A2 is a word, such as Cement, that will determine the% of duty from the duty table.Then multiply this% by the $ in cell E2. A B Cement 9.0% Synthetic 6.5% Seamless 20.5% Now i need to make cell E2 the sum of 2 columns with the first column being the price to assess duty and the 2nd column a commission that is 7% of the first column. E2=$14.98, 1st new column is $14.00, 2nd new column is $.98 Can you give me some guidance.
I am in over my head and determined to learn how to do this. Thank you so much, Debbiejj. I’m having a similar issue, but with HLOOKUP.
I add a column to the sheet with the formula and it returns a nonsensical value (i.e. Not even in the lookuparray, much less on the sheet). I made this array static and since it’s HLOOKUP I’m looking for a row value (can’t understand why adding a column would affect the calculation!!). Formula in CJ2: =(CH2-HLOOKUP(CONCATENATE(LEFT(CC2,6),” “,RIGHT(CC2,2)),’Program Summary’!$D$201:$DO$243,6))^2 Strangely, when I place this formula in another sheet (and add a column), the return value stays the same and is the value I am hoping to return. If it’s of any help, the value in CC2 is part of a PowerQuery Table.
The value in CH2 is a return value from a VLOOKUP in a table on the same sheet. Thanks for any ideas! Hi Jon, I was looking into excel to complete a project I must complete. Although I have it manually all filled out, I decided that I would present it digitally for a presentation “shock”. I have a list a services the customer can choose from a drop down list. (That’s done).
Afterwards, the customer must choose how many days he wants that specific service from another drop down list (that’s done). The rest of formulas are done, such as taxes, fees, subtotal, total etc. The part where I’m stuck is that IF the customer selects a specific service, with a specific date range, that equals a price.
I have 4 columns at the moment. First column has the the list of services. Second has the specific prices related to the services but for a date range of 1-4 days. The third column has the price for the a date range of 5-30days.
The fourth, prices for 31-100days. This means the customer can choose i.e. 16 days, regardless it must recognize the prices per service per date selection range. That number must go in a cell, which the rest of taxes etc automatically updates. I do have a fifth column which I defined as (“DAYS”) for drop down list purposes, that column contains the numbers 1-100.
So, the customer will choose a specific number from the “days” list and depending on the service requested, excel must look it up and show. Much appreciated, thanks!! Hi Mark, Great question! There are probably quite a few ways to solve this using a array formula. Here is one simple approach that uses Index, Match, and Offset.
This assumes the flavor is in A21 and the price is in A22. =INDEX($B$1:$D$1,MATCH(A22,OFFSET($A$1,MATCH(A21,$A$2:$A$10,0),1,1,3),0)) This uses the OFFSET function to return the entire row of values for the matching flavor. It then uses an Index/Match formula to find the matching price in that row. Here is an article on the that might help.
Let me know if you have any questions. Hi Jon This is awesome! Do you think you could work out one more challenging example? I have a situation where none of the three vlookup values are static and I was hoping to use the MATCH statement example above to sort this out. Part 1 is the value to lookup. Normally one would start with VLookup(A3Although the “3” is easy to lookup because it is the current row number, the “A” is challenging because this could be in any column.
![And And](/uploads/1/2/5/4/125466752/417674688.jpg)
The second statement is the lookup array. I can get around this by making the entire worksheet the range but would there be a way to use the match function to specify the range? I look forward to your reply and fantastic job!
Cheers Steve. This is OK, but it’s not necessary if you were using INDEX and MATCH instead of VLOOKUP. I learned from another Execl guru a few years ago this simple rule: Never Ever use VLOOKUP or HLOOKUP (pretend that they don’t exist); Always Always use INDEX & MATCH. I’ve found that he was right. INDEX and MATCH are much more flexible and more powerful; you can look up a value in ANY column or row (or BOTH for a 2-dimensional lookup) and find the corresponding value in ANY cell, regardless of which is to the left or right or top or bottom. INDEX & MATCH are great (they are automatically Dynamic). VLOOKUP is limited and confusing.
You make a great point about INDEX Dan. INDEX and MATCH probably are the true dynamic duo, and I am planning on writing a third post in this series that explains INDEX. I agree that the INDEX & MATCH combo really are the ultimate in flexibility. They can also cause confusion for those that have not used INDEX yet. Even though you technically could completely rely on INDEX and MATCH instead of the LOOKUP functions, I still believe the VLOOKUP is a good one to keep in your toolbox. Mostly because it is one of the most commonly used functions, and there is a higher chance that future users of your model will understand it. The VLOOKUP is also much faster to write in some cases, because you only have to write one function instead of two.
With that said, you still make an excellent point. In the next post about INDEX I will add a section that lists the pros & cons of using VLOOKUP vs INDEX & MATCH. Thank you for the comment and inspiring an idea!