Thank you, Herbert Seidenberg.
I have more than a thousand formulas as
=VLOOKUP(8,t_array,2,0)+VLOOKUP(10,t_array,3,0) on a sheet. They have
different col_index_num s when in the same row. They have different
lookup_value s and number of LOOKUP functions when in the same column. How
to change the formulas automatically?
"Herbert Seidenberg" wrote in message
oups.com...
Assuming your data, named t_array, looks like this,
9 11 21
10 13 27
1 14 25
2 13 28
6 21 28
8 18 22
3 15 25
4 19 30
5 12 27
7 14 30
and you want to find
=VLOOKUP(8,t_array,2,0)+VLOOKUP(10,t_array,3,0)
and convert it to
=R6C2+R2C3 (=18+27) (=45)
Enter this formula
="^"&"="&ADDRESS(MATCH(8,INDEX(t_array,,1),0),2,1, 0)&"+"
&ADDRESS(MATCH(10,INDEX(t_array,,1),0),3,1,0)
Select this cell and Copy Paste Special Value
Then erase the ^
I leave it to you to convert to HLOOKUP and A1 reference.