Index/Match volatility v. vLookup

[dmj120]

In your experience, is index/match better than vLookup regarding volatility?? I have another very large workbook, hence the concern.

I've tried to google and found the below from Volatile Excel Functions -Decision Models.

Excel’s Volatile Functions
Some of Excel’s functions are obviously volatile: RAND(), NOW(), TODAY()
Others are less obviously volatile: OFFSET(), CELL(), INDIRECT(), INFO(), RANDBETWEEN(), RANDARRAY()
Some are volatile in some versions of Excel but not in others: INDEX()became non-volatile in Excel 97
A number of functions that are documented by Microsoft as volatile do not actually seem to be volatile when tested:
INDEX(), ROWS(), COLUMNS(), AREAS()

 

[Peter_SSs]

is index/match better than vLookup regarding volatility??

Yes, but the effect depends on the particular circumstances.
Example

23 01 10.xlsm
	A	B	C	D	E																																																																										CA
1	a	1			a																																																																										1
2	b	2			b																																																																										2
3	c	3			c																																																																										3
4	d	4			d																																																																										4
5	e	5			e																																																																										5
6	f	6			f																																																																										6
7	g	7			g																																																																										7
8	h	8			h																																																																										8
9	i	9			i																																																																										9
10	j	10			j																																																																										10
11
12
13	d	4			d																																																																										4
14		4																																																																													4
Sample

Cell Formulas
Range	Formula
B13	B13	=INDEX(B1:B10,MATCH(A13,A1:A10,0))
B14	B14	=VLOOKUP(A13,A1:B10,2,0)
CA13	CA13	=INDEX(CA1:CA10,MATCH(E13,E1:E10,0))
CA14	CA14	=VLOOKUP(E13,E1:CA10,75,0)

In terms of volatility ..
B13 will recalculate if any cell in A1:A10 or B1:B10 or A13 changes (ie if any one of 21 cells change)
B14 will recalculate if any cell in A1:A10 or B1:B10 or A13 changes (ie if any one of 21 cells change)
No difference between Index/Match and Vlookup here

CA13 will recalculate if any cell in E1:E10 or CA1:CA10 or E13 changes (ie if any one of 21 cells change)
CA14 will recalculate if any cell in E1:CA10 or E13 changes (ie if any one of 751 cells change)
So here there is much more possibility of the CA14 Vlookup formula recalculating and if it recalculates because something in the columns that I have hidden change it is a wasted recalculation as it will not change the formula result. On the other hand if that hidden data is static & never changes then CA14 is actually no more volatile than CA13.

 

[dmj120]

Thanks Peter!!!

 

[Peter_SSs]

You're welcome.