|
Nov 2019
3:44pm, 26 Nov 2019
46,541 posts
|
GlennR
I'm not sure that was my point...
|
|
Nov 2019
4:16pm, 26 Nov 2019
1,416 posts
|
um
Go then, I'll bite .. why is Index-Match so much better?
My use to date has been on moderate data extracts (typically SKU or order number or order no & line no based) from multiple sources, typically 10-20,000 lines in each, 20-100 columns, where I want to pull together data from multiple columns to then manipulate/standardise/analyse & report. I only want exact matches and a flag (like the isna / iserror) to identify mismatches.
This is the one 'advantage' I've found online, but speed of the vlookup has never been a notable issue for me.
Suppose you have a large table with many columns of product information. And suppose you want to look up a specific SKU (or whatever) in the table and return information about it from a variety of columns within the table.
If you use VLOOKUP you must look up the same SKU for each column of information you need. Those duplicate lookups take a long time to perform.
But if you use an INDEX-MATCH approach, you could set up one MATCH formula that returns only the row-index number for the product that interests you. Then you can use any number of INDEX formulas that get their row-index number from the cell with that single MATCH formula. Both MATCH and VLOOKUP take about the same time to calculate. But INDEX works almost instantly. So if you want to return ten items for a SKU, the VLOOKUP method will take roughly ten times longer than the INDEX-MATCH approach.
|
|
Nov 2019
4:19pm, 26 Nov 2019
9,595 posts
|
larkim
It can look up values on the left as well as the right too!
|
|
Nov 2019
4:24pm, 26 Nov 2019
46,546 posts
|
GlennR
um, the sort of large-scale project finance models I have traditionally developed and operated use a lot of resources to calculate things like optimal loan repayment strategies. Volatile functions such as VLOOKUP slow things down considerably in these circumstances.
In general use INDEX MATCH is simply tidier and, under some circumstances, less prone to errors than LOOKUP.
|
|
Nov 2019
4:25pm, 26 Nov 2019
29,706 posts
|
SPR
If set up correctly, inserting columns won't break it like VLOOKUP.
|
|
Nov 2019
4:38pm, 26 Nov 2019
46,547 posts
|
GlennR
Exactly.
|
|
Nov 2019
5:25pm, 26 Nov 2019
3,652 posts
|
run free
Same with power pivot 
|
|
Nov 2019
5:26pm, 26 Nov 2019
3,653 posts
|
run free
Glenn why don’t you like power pivot?
|
|
Nov 2019
7:34pm, 26 Nov 2019
46,551 posts
|
GlennR
Because it’s not doing the job properly. Remember, I build (or built) models that other people can use by putting in a limited and controlled range of inputs, that then produce a particular set of outputs in a standardised form. I can’t rely on users to play with power pivot to get the information they need.
|
|
Nov 2019
2:24am, 27 Nov 2019
84 posts
|
mattglen_
Does anyone have any links to tutorials regarding INDEX MATCH that they’ve used in the past? I too hadn’t heard it praised so highly until this thread, time to check what the fuss is about
|
