You will notice that the result of the MATCH formula changed to “4”. In the following screenshot I inserted a column to the left of column B with a new size, “Short”. Now let's see how the MATCH function can be a little more dynamic. The MATCH function was able to lookup the word “Grande” in row 1 and return the value of 3 in cell B14. If you are looking up numbers with the MATCH function then the “Less than” or “Greater than” match types can be very useful. When your MATCH is looking up text you will generally want to look for an exact match. In this example we will use “Exact match”, which is represented by putting a 0 (zero) in the third argument. Here we specify if the function should look for an exact MATCH, or a value that is less than or greater than the lookup_value. – The match type tells MATCH how precise to be with the lookup. In that case MATCH would look down the column from top-to-bottom to find a match.ģ. You can also specify a column for this argument. MATCH will look through row 1 from left-to-right until it finds a match. I selected the range $A$1:$D$1, which contains the column header names. Here we need to tell MATCH where to look for the word “Grande”. lookup_array – This is the where argument. I have entered the text “Grande” in cell A14, so we can make a reference to cell A14 in the formula.Ģ. In this example we are looking for “Grande” in row 1. In the first argument we tell the VLOOKUP what we are looking for. lookup_value – This is the what argument. VLOOKUP MATCH Explained at Starbucks.zip Downloadġ.
#Vlookup with drop down mac excel 2013 download#
You can download the file to follow along. We will answer this question using the MATCH function. “What is the column number for the size Grande?” I'll explain why later, but for now we just want to answer the question: In this example we want to use the MATCH function to return the column number for the size Grande. Let's look at the Starbucks menu example again to learn MATCH. I explain this simple definition below as we walk through an example of creating a VLOOKUP formula. This simple definition just makes it easier for me to remember the three arguments. The following image shows the Excel definition of the VLOOKUP function, and then my simple definition. The MATCH function's arguments are similar to the VLOOKUP's. Hopefully this will help you remember and distinguish the difference between the two. Robin is the MATCH and returns a smaller value in the form of a number. They do very similar jobs, but MATCH packs a smaller punch.īatman is the VLOOKUP and returns a big value in the form of a cell's value. I like to think of MATCH as VLOOKUP's little brother, or side-kick. The difference is that it returns a row or column number. It's job is to look through a range of cells and find a match. The MATCH function is very similar to the VLOOKUP. We first need to learn how the MATCH function works. This is a problem! But fortunately for us, VLOOKUP has a side-kick named MATCH that will save the day. Therefore, the formula is now returning the wrong result.
#Vlookup with drop down mac excel 2013 update#
Excel does NOT update the formula when a column is inserted or deleted. However, our VLOOKUP still references column 3. This change means that the size Grande is now in column 4 (col D). In our spreadsheet example, we would need to insert a column after column A for the new size. Let's say they decide to add a size “Short” to the menu, and put it to the left of the size Tall. We put a “3” in the column index argument in the VLOOKUP formula to reference the Grande column (col C).īut what if Starbucks decided to add a new size to the menu? In that example we wanted to return the price for the size Grande, which was in column 3 of the menu. We can use the Starbucks menu VLOOKUP example to help explain this issue. 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. 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? The VLOOKUP is a very useful function, but it doesn't respond to change very well. This helps protect the VLOOKUP from returning errors when changes are made to the workbook. We are going to learn how the MATCH function can be used inside the VLOOKUP function.