Match function won't recognize values unless I type them
 

I am using the match function to identify values that appear in two separate
lists. However, the function will not recognize values in the second list
even though they are clearly there. If I then re-type the value in the second
list (there were originally copied and pasted from a web page) the match
function works correctly. Any ideas or ways to fix this without re-typing all
values?

Answer: Match function won't recognize values unless I type them
 

It sounds like the issue may be related to the formatting of the values in the second list. Sometimes when values are copied and pasted from a web page, they may contain hidden characters or formatting that can cause issues with functions like MATCH.

One solution you can try is to use the TRIM function to remove any leading or trailing spaces from the values in the second list. Here's how you can do it:

1. Select the cells containing the values in the second list.
2. Click on the formula bar at the top of the screen.
3. Type
   Formula:

   ---

   =TRIM( 


   ---

   and then click on the first cell in the selection.
4. Type
   Formula:

   ---

   ) 


   ---

   and press Enter.

This will remove any leading or trailing spaces from the value in the selected cell. You can then copy and paste the formula down to apply it to the rest of the cells in the selection.

Once you've done this, try using the MATCH function again to see if it recognizes the values in the second list. If it still doesn't work, you may need to check for other formatting issues or try reformatting the cells in the second list to match the format of the cells in the first list.

Match function won't recognize values unless I type them
 

Here's my standard answer for VLOOKUP(). Many of the reasons for not finding something are the same for MATCH()

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"bdp1978" wrote in message ...
|I am using the match function to identify values that appear in two separate
| lists. However, the function will not recognize values in the second list
| even though they are clearly there. If I then re-type the value in the second
| list (there were originally copied and pasted from a web page) the match
| function works correctly. Any ideas or ways to fix this without re-typing all
| values?

Match function won't recognize values unless I type them
 

Oops......
================================================== =======
Vlookup gives wrong answer

Niek Otten, April 1 2006

Frequent causes:

1. Some cells look like numbers, but are actually text. You can check with the ISTEXT function.
Check both the search arguments and the lookup table.
Formatting as numbers afterwards doesn't help.
Remedy:
Format an empty cell as Number. Enter the number 1. EditCopy. Select your "numbers". EditPaste Special, check Multiply.

2. The data is not sorted ascending and the 4th argument of the VLOOKUP is TRUE or is omitted.

3. There are spaces or other invisible characters in either the search arguments or the lookup table.
This often happens when you import data from other applications.
Use the LEN() function to see how many characters there really are in the cell and compare that with what you see.
Use the TRIM function to remove all spaces except single spaces between words.
Use the CLEAN function to remove all nonprintable characters. HTML characters can be removed with a macro by David
McRitchie,
which can be downloaded he http://www.mvps.org/dmcritchie/excel/join.htm#trimall

4. The formula was copied from somewhere else, but the addresses of the lookup table were not absolute so have changed in
the Paste process
and now point to the wrong range.
Use absolute addresses for the lookup table, like $A$1:$B$20 instead of relative addresses like A1:B20.
When editing or entering a formula, use the F4 key to toggle between several forms of relative addresses and absolute ones
(normally only for the table, not for the search argument, but this depends on your specific problem).
The first hit of F4 changes the default relative address to an absolute one. That is usually what you need.
Even better: use a Defined Name for the table instead of cell addresses; InsertNameDefine.

5. The table was extended after its initial use, but the definition of the table in the VLOOKUP or in the Defined Name was
not adjusted accordingly.
To prevent this from happening: always use explicit bottom and top rows (with dummy error values if necessary)
and insert new rows or cells between those two.
Then the definition of the range or the Defined Name will adjust automatically.
Users of Excel 2003 and higher may profit from the List feature in the Data menu to maintain tables.
================================================== =======

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Niek Otten" wrote in message ...
| Here's my standard answer for VLOOKUP(). Many of the reasons for not finding something are the same for MATCH()
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "bdp1978" wrote in message ...
||I am using the match function to identify values that appear in two separate
|| lists. However, the function will not recognize values in the second list
|| even though they are clearly there. If I then re-type the value in the second
|| list (there were originally copied and pasted from a web page) the match
|| function works correctly. Any ideas or ways to fix this without re-typing all
|| values?
|
|

Match function won't recognize values unless I type them
 

It appears Niek didn't paste in his usual information, so...

When you paste something from another source, whether web page, database
query, or even Word, Excel may see the value as a text string, even if it
looks like a number. One way to deal with it iis to copy a blank cell,
select the pasted values, and use Edit-Paste Special-Add-Values-OK to
force Excel to treat them as numbers

If you have pasted text values, names for instance, then there may be
non-breaking spaces before or after the text values. In that case, use Find
& Replace. Search for the non-breaking space by clicking in the Find what
box, holding down the ALt key and USING THE NUMERIC KEYPAD to type 0160.
Replace it with nothing

See if one of those approaches does the trick

"Niek Otten" wrote:

Here's my standard answer for VLOOKUP(). Many of the reasons for not finding something are the same for MATCH()

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"bdp1978" wrote in message ...
|I am using the match function to identify values that appear in two separate
| lists. However, the function will not recognize values in the second list
| even though they are clearly there. If I then re-type the value in the second
| list (there were originally copied and pasted from a web page) the match
| function works correctly. Any ideas or ways to fix this without re-typing all
| values?

Match function won't recognize values unless I type them
 

Thanks to both of you for your help - there was a rouge space at the end of
the copied text which was causing the problem. Thanks again.



"Duke Carey" wrote:

It appears Niek didn't paste in his usual information, so...

When you paste something from another source, whether web page, database
query, or even Word, Excel may see the value as a text string, even if it
looks like a number. One way to deal with it iis to copy a blank cell,
select the pasted values, and use Edit-Paste Special-Add-Values-OK to
force Excel to treat them as numbers

If you have pasted text values, names for instance, then there may be
non-breaking spaces before or after the text values. In that case, use Find
& Replace. Search for the non-breaking space by clicking in the Find what
box, holding down the ALt key and USING THE NUMERIC KEYPAD to type 0160.
Replace it with nothing

See if one of those approaches does the trick

"Niek Otten" wrote:

Here's my standard answer for VLOOKUP(). Many of the reasons for not finding something are the same for MATCH()

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"bdp1978" wrote in message ...
|I am using the match function to identify values that appear in two separate
| lists. However, the function will not recognize values in the second list
| even though they are clearly there. If I then re-type the value in the second
| list (there were originally copied and pasted from a web page) the match
| function works correctly. Any ideas or ways to fix this without re-typing all
| values?