Matching when spaces are involved

G

Guest

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
 
G

Guest

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(UPRNmatch.xls!Match),0),1)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Matching problem 1
Index Match to add employee number 2
Matching two columns and their data 2
Index and Match 3
When Using VLOOKUP With MATCH How Do I Handle #NA OnData Retrieval 2
When Using VLOOKUP With MATCH How Do I Handle #NA OnData Retrieval 1
When Using VLOOKUP With MATCH How Do I Handle #NA OnData Retrieval? 2
Spaces at the end of data 5

Top