Need Case Sensitive VLOOKUP FOrmula
In the attach picture I'm trying to find exact match of the value in Column C with Column A and if it finds exact match (case sensitive) it should return the value in Column B.
I've tried the following formulas but it's not working:
=VLOOKUP(MAX(EXACT(D2,$A$2:$A$9)*(ROW($A$2:$A$9))),$B$2:$C$9,2,0)
also tried =INDEX(data,MATCH(TRUE,EXACT(D2,A2:AXXX),0),3)
Appreciate the help in advance.
Capture.PNG
Last edited by jobie804; 03-15-2017 at 02:19 AM.
This tutorial might help: https://www.ablebits.com/office-addi...nsitive-excel/
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.
Jobie,
try this
=INDEX($A$1:$B$6,MATCH(TRUE,EXACT(C1,$A$1:$A$6),0),2)
Its an Array formula. Use Ctrl+shift+Enter
Manikandan Arumugam
Excel Learner
Thanks for this. But it still doesn't work. I still get NAs on the results.Originally Posted by Manikandan Arumugam
This isn't going to be much help on the solution, but I believe, with the way your data are formatted, vlookup will not work because it doesn't look backwards so you will need a different function or need to set the data up differently.
Hi,
If you are still getting #N/A on the INDEX MATCH array formula, you do not have exact matches for those rows. There may be leading or trailing spaces, for example.
Don
Please remember to mark your thread 'Solved' when appropriate.
...or you did not use CTRL-Shift-Enter...
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Thanks! I tried again and did the CTRL-SHIFT-ENTER and it is now working!
Thanks everyone!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks