Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /homepages/16/d462231950/htdocs/EFC/wp-content/plugins/wp-markdown/markdownify/markdownify.php on line 299

Warning: Declaration of WarpMenuWalker::start_lvl(&$output, $depth) should be compatible with Walker_Nav_Menu::start_lvl(&$output, $depth = 0, $args = Array) in /homepages/16/d462231950/htdocs/EFC/wp-content/themes/yoo_sync_wp/warp/systems/wordpress/helpers/system.php on line 678

Warning: Declaration of WarpMenuWalker::end_lvl(&$output, $depth) should be compatible with Walker_Nav_Menu::end_lvl(&$output, $depth = 0, $args = Array) in /homepages/16/d462231950/htdocs/EFC/wp-content/themes/yoo_sync_wp/warp/systems/wordpress/helpers/system.php on line 678

Warning: Declaration of WarpMenuWalker::start_el(&$output, $item, $depth, $args) should be compatible with Walker_Nav_Menu::start_el(&$output, $item, $depth = 0, $args = Array, $id = 0) in /homepages/16/d462231950/htdocs/EFC/wp-content/themes/yoo_sync_wp/warp/systems/wordpress/helpers/system.php on line 678

Warning: Declaration of WarpMenuWalker::end_el(&$output, $item, $depth) should be compatible with Walker_Nav_Menu::end_el(&$output, $item, $depth = 0, $args = Array) in /homepages/16/d462231950/htdocs/EFC/wp-content/themes/yoo_sync_wp/warp/systems/wordpress/helpers/system.php on line 678

Warning: count(): Parameter must be an array or an object that implements Countable in /homepages/16/d462231950/htdocs/EFC/wp-content/themes/yoo_sync_wp/warp/systems/wordpress/helpers/system.php on line 61

Warning: session_start(): Cannot start session when headers already sent in /homepages/16/d462231950/htdocs/EFC/wp-content/themes/yoo_sync_wp/warp/systems/wordpress/helpers/config.php on line 48
Data Handling | Excel For Consultants

Speeding up VLOOKUP (and MATCH) formulas by orders of magnitude!

Written by Peter Albert on . Posted in Data handling, Expert, Modeling

Executive summary

By default, you’re using FALSE as the last parameter of a VLOOKUP or MATCH formula to make sure that the proper result is returned. This approach is really slow on larger data sets. Alternatively, sorting the data and using TRUE will increase the overall search speed by a very large factor!!!

This post will explain how and when to use this technique – and what to watch out for.

Quick reference

  1. Sort your data ascending according to your key column (the first column of your VLOOKUP range)
  2. Replace your =VLOOKUP(A1,Sheet2!$A:$B,2,0) with =VLOOKUP(A1,Sheet2!$A:$B,2) (you can omit the TRUEas last parameter as this is the default)
  3. Watchout: In case you are not sure if A1 is contained in your list, use this formula: =IF(VLOOKUP(A1,Sheet2!$A:$B,1)=A1,VLOOKUP(A1,Sheet2!$A:$B,2),NA())

Detailed explanation

Use INDEX and MATCH instead of VLOOKUP

Written by Peter Albert on . Posted in Advanced user, Data handling

Executive summary

Replace your VLOOKUP formula with a combination of INDEX/MATCH. There are only upsides to it – apart from the minor learning effort ;-)!

Quick reference

Replace

=VLOOKUP(YourKey,YourTable,ColumnNumberToReturn,0)

with

=INDEX(ColumnToReturn,MATCH(YourKey,ColumnWithKey,0))

E.g. replace =VLOOKUP(G1,$B$2:$D$5,2,0) with. =INDEX($D$2:$D$5,MATCH(G1,$B2$2:$B$5,0)).

If you need to retrieve multiple columns for one key, extract the MATCH part of the above function to another cell and refer all INDEX functions to this cell.

Detailed description