The row_num is the relative row number of the cell you want. The array is the table of data that contains the cell value you want.
The syntax for INDEX is as follows: =INDEX(array, row_num, )
The INDEX function takes a location and returns the value that is in the cell. The optional match_type determines whether MATCH must find the lookup_value exactly (with a 0), or return the closest match that comes before it (with a 1) or after it (with a -1) alphanumerically. The lookup_array is the array of values you are trying to find the lookup_value in. The lookup_value is what you are searching for. The syntax for MATCH is as follows: =MATCH(lookup_value, lookup_array, ) MATCH is a function that gives you the location of an item in an array. This means, we will need to lookup the gadget make year, company, product and size at the same time to find the appropriate price.Īlso read: Using INDEX and MATCH to Replace VLOOKUP Fortunately, in this case, the combination of product criteria can serve to separate them. Its is impossible for naked VLOOKUP to get the price of a desired model as there are many companies, many gadgets, for instance Dell 7″ Tablet has a price change in years. Normally, when we enter the model we would want to get the price of a gadget as a result. Here’s a quick tutorial to help you learn how…ĭownload Example File at the end of article.įor example, we want to get the price of specific gadget from the list of gadgets stores data… Fortunately Excel has functions called INDEX and MATCH that works same as VLOOKUP but can do wonders with multiple criteria lookup. What If you need to refer two or more conditions to match a specific piece of data, VLOOKUP will only get the first match in the data. VLOOKUP is definitely a great tool to get the data from tables, however it has limitations: it can only work with one criteria for matching information.