July 20, 2019
I have a table that needs to look into another table for values. Rather than Vlookup I'm using OFFSET(Ref, Match(), 0) which used to work well. This is relevant because the Ref column is to the left of the Match column. There are approximately 160 entries in each column. I've entered the formula in a table (actually just a normal range) that has perhaps 60 rows. However, the wonders of the Dynamic formula mean Excel thinks I want to return 160 values and cleverly fills them all in - which I don't wont.
Additionally, if I try to Evaluate Formula it refers to the 'source' of the formula and I cant therefore see what is happening to cause a problem on some of these rows.
I've only seen this this morning and I'm already thinking of reverting to some variant that doesn't try and second guess what I want.
Grateful for any advice.
December 7, 2016
As I don't know what version of Excel you are using, but see if you can use XLOOKUP function instead.
November 1, 2018