compare two lists for matches excel 2016 mac

One of the key advantages of INDEX and MATCH over the VLOOKUP function is the ability to perform a 'left lookup'. Video: How to debug a formula with F9 (to see MATCH return values) Left lookup Video: How to do a two-way lookup with INDEX and MATCH Note: you could use Data Validation to create dropdown menus to select salesperson and month. For example, to return the February sales number for Frantz, we provide the range C3:E11 with a row 5 and column 2:Īnd INDEX correctly returns $10,525, the sales number for Frantz in March.

From the discussion above, we know we can give INDEX a row and column number to retrieve a value. Let's say we want to write a formula that returns the sales number for February for a given salesperson. Now that we've covered the basics of INDEX and MATCH, how do we combine the two functions in a single formula? Consider the data below, a table showing a list of salespeople and monthly sales numbers for three months: January, February, and March. Match type defaults to 1, which means approximate match, so it's important to provide a value. In many cases you will want to use zero (0) to force exact match behavior. Match type is important and controls whether matching is exact or approximate. Important: The last argument in the MATCH function is match type. Video: How to use MATCH for exact matches

Same result with a horizontal range, MATCH returns 3.

Compare two lists for matches excel 2016 mac