Log In
Register
Home
July 20, 2019
Offline
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.
Regards
Peter
VIP
Trusted Members
December 7, 2016
Offline
Hello Peter,
As I don't know what version of Excel you are using, but see if you can use XLOOKUP function instead.
Br,
Anders
Trusted Members
Moderators
November 1, 2018
Offline
If you only want one value back from each formula, either change the Ref to only be the first cell in the table column, or use OFFSET(Ref, Match(), 0,1)
Better still, avoid the volatile offset function and use INDEX(Ref,Match())
1 Guest(s)
