Hi everyone.
I've just started using Excel forms online and ive hit a road block. Ive used the Google Docs platform in the past, so Im familiar with the workings of forms.
My problem is the formatting of Form responses is always "text". I cannot use VLOOKUP to pull info from responses when they are in text format. Is there a way to format responses a different way, or pull info differently but with similar results as VLOOKUP?
Thanks!
Chris
Try converting the lookup cell's contents to a numerical value like this:
=VLOOKUP(VALUE(lookup_cell),lookup_range,N,0)
or this:
=VLOOKUP(--lookup_cell,lookup_range,N,0)
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.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi Ali. Thank you very much for the reply, but im still stuck. Maybe my formatting is wrong?
Ive attached an example sheet.
Thanks again!
Chris
There is no 'Down' in column C, so it's not going to return anything. What are you expecting?
Hi Ali. Sorry, was messing around with it and didnt send it saved correctly. Even when I change the value in Cell E12 nothing changes.
It works here, so it's something at your end - check that your calculation options on the Formulas tab are set to automatic. It works, by the way, without the VALUE function.
The issue is with the lookup array, anyway, not the lookup value. The 4 is text - it needs converting to a number.
You could use this, though:
=VLOOKUP(TEXT(E12,"0"),C:D,2,FALSE)
Last edited by AliGW; 01-17-2019 at 09:56 AM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Ok so it appears the problem is the way that the form posts responses. Unless I copy and paste a cell that works into a cell that doesnt work, I cannot get a formula to work consistently. I havent touched a cell format, they stay however they are created. Some work and some dont. Even tried pulling the results to another tab. Some still work some dont. Currently using Names not numbers. I understand the 4 is text and needs to get converted, but in lieu of me doing that for 200 entries over the course of 5 days, is there another way to have all responses a # instead of text? Initially the calculations option was set to manual, but I set that to Automatic and while it fixed it in the beginning, as I add lines of response from the form it doesnt work any longer.
Did you try the suggestion in post #7? If that doesn't work, perhaps you could provide a sample with more data showing the problem.
Here you go! So it started working, than the last two entries from the form, Hollywood and Philadelphia, dont work on the vlookup. However the word Down works just fine. Ive also used the formula from above in column H with no luck. Thank you again for your help!
The problem is that Hollywood has an invisible return character after it, so Excel is not seeing a match. Remove that, and the formula works. This is what we call dirty data!
Try this:
=VLOOKUP(TEXT(F11,"0")&"*",C:D,2,FALSE)
Last edited by AliGW; 01-17-2019 at 03:04 PM.
Ok so 2 things...1-your formula fixed it, thank you very much! 2-The dirty data makes perfect sense. I am copy/pasting a large amount of entries into my form and it looks like thats where the dirty data is coming from. Once I type it in by hand it works perfect!!
Thank you so much!
Chris
No problem!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks