Hi all,
I'm having trouble with an index match formula. My lookup values look like one of these two types:
092GSW015
103P 345
Whenever the formula hits the second value (with the space in the middle) it returns #N/A. I've spent a lot of time trying to figure out how to fix or use a different formula, but so far have had no luck. So now hoping someone smarter than me (not hard!), can help.
Any suggestions?
Note user level = beginner
No problem with VLOOKUP or INDEX/MATCH for your search values.
HTML Code:
Regards, TMS
Last edited by TMS; 03-18-2014 at 06:45 PM.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Hmm, yes, very strange. I can repeat your example in a new sheet as well. I wonder what I've done wrong.
My array and lookup_array (for index and match formulas) are located in a separate workbook. I can't imagine that would be the problem. Cells are all formatted as "general" if that makes a difference. Strange.
How would you like us to diagnose the problem?
Not sure, but here's some more information that may be helpful. If I go to my test spreadsheet which I set up similar to your first response, then highlight column by clicking the "A" at the top, then click "Ctrl-F" and enter the search string "103P 345" and hit enter, it jumps to cell A2. Indicating it found the text string.
However, in my working spreadsheets when I do the same thing, for any text string that has the space in the middle the search function returns "Microsoft Excel cannot find the data you're searching for."
Perhaps I have a setting that needs to be changed?
Maybe you have leading or trailing spaces, either in the search string or the lookup table.
Alternatively, the "space" in the middle may not be a true space but a non-printable character.
For example, type: =CHAR(160) into cell A1. It will not display anything. Test the length of the cell =LEN(A1). Copy it and Paste Special | Values into cell A2. Check the length of that cell. What do you see? Looks like nothing but it has a single non-printable character.
This typically happens with data from a web site.
Try deleting the space in the middle of one of these cells and retyping it. Then search for it again.
Regards, TMS
Last edited by TMS; 03-18-2014 at 07:34 PM.
Or you might have a double space in the middle (difficult to spot that).
Pete
That was it! I used the trim function and now everything works properly. Thanks all, much appreciated!
You're welcome. Thanks for the rep.
What was it? Trailing spaces? Leading spaces ...?That was it!
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
Not sure if it was trailing or leading spaces, but when I applied the trim function then copied and pasted the results overtop, the formulas all worked and the error notation disappeared. I've completed a rough spot check comparing the results manually to the data table and everything is correct. Very glad this is working now and can move on to the rest of my work!
Cheers!
Thanks for the clarification and update. Glad you have a resolution.
Regards, TMS
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks