Therefore, because the SKU A-25 doesn’t exist in the database, both return their best guess for the value we want…a guess that is incorrect in this case. Here, both functions still use an approximate match. So what if we look for a SKU that doesn’t exist? (Because the match_type is omitted, MATCH also performs an approximate match in this instance.) Then the INDEX function returns the value for the specified row-index value from the Sales column. MATCH searches the SKU column for the value in cell F3 and returns the row-index number where the item is found. Because the range_lookup value was omitted, VLOOKUP uses an approximate match, which requires the data to be sorted in ascending order. It looks in the left-most column of MyData for the value in cell F3 and returns the results from the 3rd column of MyData. VLOOKUP relies on a reference to the entire database, MyData. When we use INDEX and MATCH together, the MATCH function finds the lookup_value’s row-index or column-index number and then passes this information to the INDEX function, which returns the information we actually want. =MATCH( lookup_value, lookup_array , match_type) =INDEX( range , row_index_num, column_index_num) =VLOOKUP( lookup_value, table_array, col_index_num , range_lookup) Here are the three functions we’re working with: The following figure shows a small database in columns A-C and several uses of both VLOOKUP and INDEX-MATCH in the remaining columns. To make formulas easier to talk about, I’ve assigned the following range names: Just send them a link to this post! A Quick Introduction to VLOOKUP and INDEX-MATCH If that’s the problem, now is the time to point them in the right direction. Second, you might be working with Excel beginners who sort of understand VLOOKUP but who know nothing about INDEX-MATCH. If that’s the problem, I hope this post will help.
How to use vlookup in excel to combine two spreadsheets how to#
I can think of only two reasons you ever should use VLOOKUP (or HLOOKUP, which does the same thing, but sideways). First, you might not know how to use INDEX-MATCH. At its worst, INDEX-MATCH is slightly faster than VLOOKUP at its best, INDEX-MATCH is many-times faster. INDEX-MATCH is much more flexible than Excel’s “lookup” functions.Ģ. Excel’s VLOOKUP function is more popular than the INDEX-MATCH function combination, probably because when Excel users need to look up data then a “lookup” function would be a reasonable choice to make.ġ.