Hello All,
My Match function is not working when my lookup value references another cell. Here is my formula:
=MATCH(G26,$P$9:$AC$9,0)
Where my G26 is =G11 which is a specific text string. When I just type in the text itself into G26, the formula works. However, I need G26 to populate based on potential changing values in G11. Any thoughts?
It might be helpful for us to look at an example Excel file (redacted) that demonstrates the problem. You can attach an Excel file (not an image) to this thread by clicking Go Advanced and clicking Attachments on the toolbar.
My guess: the text in G11 has extraneous characters -- perhaps extraneous spaces or HTML nonbreaking spaces (ASCII 160) -- which you avoid by entering what you think the text is directly into G26.
For example, does =LEN(G11) show the same number of characters as you believe are displayed in G11?
What does "not working" mean with this function? I can assume that it means "returning an N/A value because the text string in G26 (and G11) is not found in P9:AC9, but it would help to be certain that this is the case.
If it is not finding the text string in G26 (and G11) in that range, that means that whatever text string is in G11 is not present in P9:AC9. Recognize that the "exact match" 0 in the 3rd argument really means "exact match" -- no difference allowed. First things to check for are leading trailing spaces or other non-printing characters. Second, be sure to check the text string carefully (both in G26/G11 and in P9:AC9) until you are certain that they are exact matches.
From there, I'm not sure there is much to be said without a sample spreadsheet so we can see exactly what is in G11, G26, and P9:AC9.
Originally Posted by shg
Thank you both for the quick response! The error was indeed a N/A value. You were correct, I had an extra space in the reference cells. Thanks so much for your help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks