Dears,
I failed to get the full name of the student by looking at the full listings file with using Vlookup.
It shows #REF any time.
Attached is an examples.
getfull.xls - the enquiry file
nlookup.xls - the full lists
Anybodies could help me?
Getfull.xlsnlookup.xls
Kevin
you are looking in the 3rd column...try changing it to 2, instead of 3
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
The reason for the #REF error is the range in your formula covers only 2 columns B & C and you mentioned 3 as output column
=VLOOKUP(B4,[nlookup.xls]Sheet1!$B$1:$C$5,3,FALSE)
So it should be
Formula:
Please Login or Register to view this content.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
it solved. thanks,
How to rewrite the formula to show the class no. instead the full name?
thanks,
Kevin
try this, copied down...
=INDEX('[nlookup (1).xls]Sheet1'!A$2:C$5,MATCH(B4,'[nlookup (1).xls]Sheet1'!$B$2:$B$5,0),1)
It works although I don't familiar why the rewrote formula details and its meanings. I am freshOriginally Posted by FDibbins
a vlookup cant look "behind" itself. you want to find data that is to the left of the column with the criteria in it.
The easiest way to do that is with index/match/match
=index(range,row,column)
=index(range-your-data-is-in,match(criteria-you-want,column-range-the-criteria-is-in,0),match(criteria-you-want,row-range-the-criteria-is-in,0) 0 returns an exact match
Hi FDibbins,
The provided code is failed to work if the input was incompleted.
examples,
type MAN --> work
type M* --> show same result as MAN, it is an unexpected result.
Any idea?
You can use wild card
from Fdibbins formula notice the additional --> Sheet1!B4&"*"
Formula:Please Login or Register to view this content.
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
The formula "failed to work", because it does a very specific search...it searches for "Man", and "M" does not equal "Man"
As Vlad suggested, if you want to search on a partcial match, use the wild-card method
I tried the code from Vlad but does not work when input M*.
The class still show 1A, Full name will become #N/A.
if you still have the full name in B, then M* will not work. try this instead...
=INDEX('[nlookup (2).xls]Sheet1'!A$2:C$5,MATCH(LEFT(B4,1)&"*",'[nlookup (2).xls]Sheet1'!$B$2:$B$5,0),1) (make sure the file names are still the same)
Hi FDibbins,
I tried your code on every cells and but looks strange
getfull.JPG
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks