=IF(VLOOKUP(lookup_value, table_array, 1, TRUE) =lookup_value, If it finds an exact match it will return the lookup value, otherwise it will return a different value. The section highlighted above carries out an approximate match VLOOKUP calculation on the first column. VLOOKUP(lookup_value, table_array, col_index_num, TRUE),NA()) =IF( VLOOKUP(lookup_value, table_array, 1, TRUE)=lookup_value, First, Let’s consider the first VLOOKUP statement. Let’s break this formula down into understandable sections. VLOOKUP(lookup_value, table_array, col_index_num, TRUE),NA()) How does this work? If there is no matched value it will return an #N/A result.Īn exact match VLOOKUP would be defined like this: =VLOOKUP(lookup_value, table_array, col_index_num, FALSE)īut combining two VLOOKUP’s to create an equivalent to an exact match looks like this: =IF(VLOOKUP(lookup_value, table_array, 1, TRUE)=lookup_value, This trick involves combining two approximate matches together to return the equivalent of an exact match. However, if the lookup value is not in the list it will return an incorrect result. An approximate match only works with sorted data but can find the lookup value faster. including TRUE as the last argument) is very fast in comparison, as it does a binary search. It will search through each record one-by-one until it finds a match. including FALSE as the last argument) is a slow method of calculation. But I learned this little trick – which provided almost unbelievable speed increases. I hardly ever use an approximate match with VLOOKUP due to the risk of getting an incorrect result.
0 kommentar(er)
