![]() 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.
0 Comments
Leave a Reply. |