Matching when spaces are involved
 

Afternoon

Im currently matching up two tables of data using the Index function.
=INDEX(UPRNmatch.xls!Gaz,MATCH(N3669,UPRNmatch.xls !Match,0),1)
(Where Gaz & Match are range names).
This is working mostly fine, however, it is failing to match some data.
Upon investigating it seems that this is when there is a space at the end of
the data field in one case, but not the other. Does anyone know how to make
the search ignore any space at the end of a data field?
The data it is comparing is addresses, so the unwanted space is at the end
of the address.
Cheers

Matching when spaces are involved
 

One way

use the TRIM() function to eliminate the spaces at the end (it'll also
change double spaces within the text to single spaces)

Another way

This is an arrya formula, so enter it with Shift+Ctrl+Enter

=INDEX(UPRNmatch.xls!Gaz,MATCH(N3669,TRIM(UPRNmatc h.xls!Match),0),1)

"JaB" wrote:

Afternoon

Im currently matching up two tables of data using the Index function.
=INDEX(UPRNmatch.xls!Gaz,MATCH(N3669,UPRNmatch.xls !Match,0),1)
(Where Gaz & Match are range names).
This is working mostly fine, however, it is failing to match some data.
Upon investigating it seems that this is when there is a space at the end of
the data field in one case, but not the other. Does anyone know how to make
the search ignore any space at the end of a data field?
The data it is comparing is addresses, so the unwanted space is at the end
of the address.
Cheers