So, I thought I somewhat mastered simple INDEX MATCH FUNCTION. The function works for some and not others cells.
I use: =INDEX(Helper!B:B,MATCH(B2,Helper!A:A,0)) and do the ctrl + shift + enter
Worksheet "DATA" A2 has a #NA but A3 gives me the results.
I checked the logic and the result should be "PALM BEACH MARKET"
Is there an explanation or is this a glitch in the function?
Thank you,
Last edited by rogrand; 08-08-2021 at 07:56 PM.
You are getting the error because B2 is a number, whereas B3 is text as is the value on the helper sheet.
I've also noticed that you have a mix of numbers & text on the helper sheet.
Also that formula does not need Ctrl Shift Enter, just Enter is fine.
Hello Fluff13, not following. All cells are are formated as "General"
in both sheets (columns B and A) use text-to-columns >>>>general and it will work.
That doesn't mean anything. The format of a cell does not change the underlying value.All cells are are formated as "General"
If you putinto g2 & copy down to G3 you will see that one returns true, the other false.Formula:
Please Login or Register to view this content.
Hi again, you just spoke in different language to me lol. Will have to learn how to incorporate the Isnumber function. Still it does not make sence.
If the cell below it has the same format and works why can I make the above do the same thing? I would prefer to put the same conditions on the cell that does not work as the one that does work and be able to use the formula that I worked hard to understand
It isnt the same format as the one below - thats the point. One is a number and the other is text.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
John, I converted all text to the columns and it did not work.
Try changing the formula to this:
=INDEX(Helper!B:B,MATCH(TEXT(B2,"#####"),Helper!A:A,0))
Data! A2 return should be "Palm Beach Market" but get #N/A. when I click on the cell it shows as "General"
Data! A3 has a return of "Palm Beach Market" and when you click on the Cell it has "General"
Data! B2 has number
Data! B3 has number
So why does Data! A2 not behave the same as DATA! A3 in which it has the same conditions.
Last edited by rogrand; 08-08-2021 at 04:58 PM. Reason: correcting information
No - B2 is a number and B3 is text. Change both of them to Number instead of General and you will immediately see that they behave differently.
Ive given you a workaround above.
My solution attached.
Would then be simpler then to convert B2 into a number. The how to escapes me. Please show me. I would love to understand this.
See above - posts #10 and #13.
B2 is already a number. B3 is text.
I see what you did.
You entered in the formula a text function to force it and it works for that cell and all the rest.
I still was in frustration even though I learned something new.
I tinkering I selected the Cell and from the dropdown I changed its status. It took a bit to take effect but it work!!!!!! So I can stay with my original formula.
So strange that doing this selecting the whole column did not work.
Again, THNAK YOU! My sanity stays with me till the next Journey.
Last edited by rogrand; 08-08-2021 at 05:29 PM. Reason: Recanting the comment to add different info
I hadn't seen this directly addressed anywhere and it seems to be the crux of your issue, how to change text to numbers.
If that is so, then this simple process will work for you.
Highlight the columns in question (in your case col B of Data tab and col A of Helper tab) go to the data tab at the top of excel, open text to columns, if delimited is selected just hit finish and they'll all convert to numeric. Hope that helps you with this issue going forward.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks