The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A. MATCH finds the smallest value that is greater than or equal to lookup_value. The values in the lookup_array argument can be in any order.
MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example. MATCH finds the largest value that is less than or equal to lookup_value. The following table describes how the function finds values based on the setting of the match_type argument. The default value for this argument is 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want. The value that you want to match in lookup_array. The MATCH function syntax has the following arguments: For example, you might use the MATCH function to provide a value for the row_num argument of the INDEX function. It will save you time and errors in the long run.Tip: Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself. I recommend trying the INDEX MATCH combination wherever you would normally use VLOOKUP. It also becomes easier to copy and paste this formula into more than one column if you want multiple values returned. Instead of a relative column number, you have the actual column written out. This combination of INDEX and MATCH is a lot clearer about where the return data is located than VLOOKUP. Since we are only giving INDEX one column, the relative column number will be 1. We just figured out the relative row number with our MATCH formula above. Return column is straight-forward, this is the same as with VLOOKUP. INDEX returns the value in a cell when given its relative position.įor our purposes, there are three parts to the INDEX function: return column, relative row number, and relative column number.
Since we want the actual value in the cell, we wrap MATCH in a function called INDEX. So far MATCH is a lot like VLOOKUP, the only difference is it will return only the relative position, not the value in the cell. We only want an exact match to be returned so the match type should be 0. We want it to return the relative row number from the lookup column. The MATCH function returns a relative position for data that matches the lookup value you give it. Using the INDEX and MATCH formulas together is little more complicated that VLOOKUP, but not terribly so and it is a lot more versatile. When the new column is added, it can shift the index number you used to get your return value and presto your formula is broken.
Also, if your table is a work in progress (and they usually are), you might want to add a new column. In our example, we can lookup up the Department name using the Department number, but we couldn’t do the reverse.
It can only return a value that is to the right of the column that contains the lookup values. Note that if there is no match, the formula returns an error so you might want to enclose VLOOKUP in the IFERROR function:Īs great and powerful as the VLOOKUP function is, it does have some downsides. Match type: This number tells Excel how you want to match. In our example, we want the value in the 3 rd column to be returned. The first column in the region you selected is 1, the second column is 2, and so on. In the example below, we start with A7 and go to D18.Ĭolumn index for return data: This is an index number for the column with the data you want returned. The leftmost column must be the one you want to search for a matching value. Region with data: This is the range of the table with the data to be matched. In the example below, this is the department number in cell A3. Lookup value: This is the cell number of the data you want to use to match. The formula itself is pretty straight forward: For example, you can match the budget information in one table with department information in another using the common department number. VLOOKUP is legitimately a great function with lots of uses. As an intermediate Excel user, you have probably used the VLOOKUP function in Excel to match data between two different tables.
0 kommentar(er)
