Question description:
This user has given permission to use the problem statement for this
blog.
I need a VLookup to recall a number in column F which relates to a name in another sheet
Solved by I. D. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
06/03/2018 - 11:55
Welcome to excel got it pro
Excelchat Expert
06/03/2018 - 11:56
Would you please share the sheet? so that I can create the vlookup formula.
User
06/03/2018 - 12:00
So in Sheet 3, Column B needs to recall data in Sheet 1 Column F and Sheet 3 Column C needs to recall Sheet 1 Column W
Excelchat Expert
06/03/2018 - 12:00
A question.
User
06/03/2018 - 12:01
Sure
Excelchat Expert
06/03/2018 - 12:01
You have students names as A B C A B C, does it replicate the original file?
User
06/03/2018 - 12:01
Yes
Excelchat Expert
06/03/2018 - 12:01
I mean duplicate in student name or repetitive names?
User
06/03/2018 - 12:01
Just changed sheet to A-I
User
06/03/2018 - 12:02
Not duplicate surnames
Excelchat Expert
06/03/2018 - 12:02
Okay, thanks.
Excelchat Expert
06/03/2018 - 12:02
I need a couple of minutes.
User
06/03/2018 - 12:02
I've split the column to first names and surnames, original has surnames in column B
Excelchat Expert
06/03/2018 - 12:05
there you go. vlookup applied to column B and C.
Excelchat Expert
06/03/2018 - 12:05
Please check and let me know.
Excelchat Expert
06/03/2018 - 12:05
for col B =VLOOKUP(A3,Sheet1!A:F,6,false)
Excelchat Expert
06/03/2018 - 12:05
for col C
Excelchat Expert
06/03/2018 - 12:05
=VLOOKUP(A3,Sheet2!A:W,23,false)
User
06/03/2018 - 12:05
Amazing thank you.
Excelchat Expert
06/03/2018 - 12:05
anything else you want to know about the solution?
User
06/03/2018 - 12:05
Could you explain to me how that works very briefly?
Excelchat Expert
06/03/2018 - 12:06
sure.
Excelchat Expert
06/03/2018 - 12:06
so, the syntax of vlookup is =VLOOKUP(lookup-value, lookup-range, column number, lookup type)
Excelchat Expert
06/03/2018 - 12:07
first lookup-value : in this case lookup value is name, which is A3 of sheet3
User
06/03/2018 - 12:07
ok
Excelchat Expert
06/03/2018 - 12:08
second lookup-range : lookup range is the range that contains the lookup-value we are searching and the result we want to display.
Excelchat Expert
06/03/2018 - 12:09
In this case the lookup range is Sheet1!A:F
Excelchat Expert
06/03/2018 - 12:09
cause sheet1 column A contains names and column F contains numbers that we want to return against names.
Excelchat Expert
06/03/2018 - 12:09
Does it make sense?
User
06/03/2018 - 12:09
Yes it does
User
06/03/2018 - 12:10
i'm with you!
Excelchat Expert
06/03/2018 - 12:10
great.
User
06/03/2018 - 12:11
then?
Excelchat Expert
06/03/2018 - 12:12
third column number : column number is the index of the column of the lookup-range from where we want to return result. Our lookup range was A:F, so if we start counting index from column A as 1, then column F gets the index number 6, right? hence I wrote 6 in the formula.
User
06/03/2018 - 12:12
right
User
06/03/2018 - 12:12
and the last one?
Excelchat Expert
06/03/2018 - 12:13
hence, for the second formula column W gets the index 23.
Excelchat Expert
06/03/2018 - 12:13
yeah, the last one. match type.
User
06/03/2018 - 12:13
why is it false?
Excelchat Expert
06/03/2018 - 12:13
or lookup type.
User
06/03/2018 - 12:13
not true?
Excelchat Expert
06/03/2018 - 12:14
If we wanna perform a exact match of names then the type is false.
Excelchat Expert
06/03/2018 - 12:14
But if it is a partial match then the lookup type should be true.
User
06/03/2018 - 12:14
so what is true?
Excelchat Expert
06/03/2018 - 12:14
just answered.
Excelchat Expert
06/03/2018 - 12:14
:)
User
06/03/2018 - 12:14
so what is a partial match?
Excelchat Expert
06/03/2018 - 12:14
lets say name is Aa
Excelchat Expert
06/03/2018 - 12:15
now is we search with A, then we would get a match with exact match.
User
06/03/2018 - 12:15
Oh i see
User
06/03/2018 - 12:15
thanks
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
