I would use Index/Match over VBA.
Just to clarify the approximate match issue you raise, using this example
(for your own future reference):
=If(Isna(Vlookup(Item, Table, 1, 1)),"missing",If(Vlookup(Item, Table, 1,
1)=Item, Vlookup(Item, Table, 2, 1), "missing"))
Approximate match lookups will return N/A if the first item in the table is
greater than the item being looked up. Hence the first test
If(Isna(Vlookup(Item, Table, 1, 1)),"missing"........)
If the item does not exist, the lookup finds the largest value that is
smaller than the Item being looked up. That is why I have a second test to
determine if the key from the table that it matched the item to is equal to
the item being looked up by returning the first column from the table:
If(Vlookup(Item, Table, 1, 1)=Item, Vlookup(Item, Table, 2, 1), "missing")
If an exact match is found (data returned from the first column of the table
= Item), it will perform the lookup and return the entry from the second
column - which is the actual data that I want:
Vlookup(Item, Table, 2, 1)
Same concept for the approximate match INDEX/MATCH approach. In this
fashion, approximate match lookup functions can be made to perform exact
matches, but often many times faster than an exact match lookup. But your
approach depends on whether or not you are able to sort the table, can set up
a helper column, whether or not you intend to hardcode the data after the
lookup is finished (no sense in spending 5 minutes to sort and set up helper
columns to save 3 minutes of calculation time), and how often the process
needs to be repeated.
If you want the cells to actually dispaly #N/A instead of "missing" if the
item does not exist in the *sorted* table:
=If(Vlookup(Item, Table, 1, 1)=Item, Vlookup(Item, Table, 2, 1), NA())