Vlookup with dynamic named range

AFAIK the Name Ranges would need to be static I'm afraid.

VLOOKUP isn't that inefficient so a relatively large static range shouldn't be too bad... (everything's relative though)

It you opt for a static range then if you can sort the data by the criteria range (eg Col A) such that you can use range_lookup of 1 rather than 0 that would help significantly (meaning large ranges not necessarily a huge issue).
(you may need to conduct a 2nd check to est. exact match but 2 tests using binary search algorithm will generally be quicker than one exact match test esp. over a large range)

What would definitely be worthwhile is considering how to avoid double evaluation for sake of error handling.

How best to that would largely depend on consistency of data type of the values being returned (eg always number, always text etc...)

If there's no consistency you will find that having one matrix do the retrieval (less double evaluation) and another to do the error handling (by referencing the first) will speed things up as there's only ever one trip to the target file.


I guess other workarounds could include using VBA and the BeforeClose event to modify the RefersTo such that it becomes static based on data at the point of closure and subsequently use the Open event to reinstate the Dynamic RefersTo ... and so on and so forth ... but that's quite a lot of work (obviously)...

ya...., that sounds complicated, lol. I just extended the named range for extra 500 rows because there are relatively few new entries entered.
Thanks anyways for the reply DonkeyOte!!