Dear all,
Please find the attachment in which i have mentioned all the details about the error in VLOOKUP function. I couldn't understand why I am getting that error for that single Vlookup value while others are ok.
Thanks in advance !!!Vlookup error.xlsm
your missing the last criteria
Formula:
Please Login or Register to view this content.
this should work
Formula:Please Login or Register to view this content.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Thanks Humdingaling.............
I got the point,but with the same criteria( except the optional one) I'm getting the values for the others (Bikhs,Jenefer,selena,Xoxom) while for the last one (Andy) there is an error. I couldn't understand for which reason the error is occurring. Do you have any idea about this ?
Changing the last parameter to False or 0 made the function work for me, even for Andy.
To answer your question. From Help (below). Since you omitted it looks at the first value Bikhs is >Andy so it returns an error.
If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.
Last edited by Hawkeye16; 07-28-2014 at 03:42 AM.
Despite the high cost of living, it remains very popular.
Don't forget to mark threads SOLVED when you get an answer and rep all the geniouses that helped you today!
I'm not 100% sure but it could be happening because:
If range_lookup, the optional criteria, is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value. (Copied from MS Excel Help)
The first four names are in Ascending order, B, J, s, X; but the 5th isn't. It is a good habit to provide the 4th criteria for both VLookUp & HLookUp when looking for an exact match, especially a string.
HTH!
yes, that's working .....but without that parameter, the result for all are correct except for Andy.....
your table is not sorted in ascending order
http://www.techonthenet.com/excel/formulas/vlookup.php
B>J>S>X are in order
if you mix them up it will either give you the wrong answer or N/A
wrong answer if there is something "similar"
N/a if nothing is similar
For Example
if you sort by EMP ID
only Selena and Xoxom will get you the right answer
because J is before S so jennefer does not work
and both Bikhs and Andy come before Xoxom
Thank you all.....
Yes, Jewelsharma.....you r correct, for that reason only...Now i got it......
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks