Excel - How to Use a VLOOKUP Formula - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

i need help with a formula that allows me to find the same value in another workbook then enter another value from the row it find the match. i know vlookup allows me to find the match but i dont know how to get the other value after it find the match.
Solved by B. H. in 51 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 12/03/2018 - 12:56
Hi, It's pleasure to help you today.
User 12/03/2018 - 12:56
hello
Excelchat Expert 12/03/2018 - 12:57
I have read your description and requirements says, you want to get value from the other column in the same matching row?
User 12/03/2018 - 12:58
yes
Excelchat Expert 12/03/2018 - 12:58
The possible answer is: Index function. I will show an example in the preview.
User 12/03/2018 - 12:59
that'd be great
Excelchat Expert 12/03/2018 - 01:01
I have added a small table in sheet2, where first column is Category and second one is value.
Excelchat Expert 12/03/2018 - 01:01
Now, I am trying to find the category mentioned in the Sheet1
Excelchat Expert 12/03/2018 - 01:02
With Match formula, i have calculated the row matching in sheet2, column A.
Excelchat Expert 12/03/2018 - 01:03
I will use this for index formula to get the value from column B matching row 2.
Excelchat Expert 12/03/2018 - 01:05
Now, you can see the value as 100.
Excelchat Expert 12/03/2018 - 01:06
I have used Index formula and it can be defined as follows:
Excelchat Expert 12/03/2018 - 01:06
Index(Range, matching row and reference column number)
Excelchat Expert 12/03/2018 - 01:07
When both match and index are combined:
Excelchat Expert 12/03/2018 - 01:07
=index(Sheet2!A:B,match(B1,Sheet2!A:A,0),2)
Excelchat Expert 12/03/2018 - 01:07
I have replaced B2 with actual match function.
Excelchat Expert 12/03/2018 - 01:11
Hope, you got the idea of applying formula?
User 12/03/2018 - 01:13
I believe this is what I need I'll try throw it in my spread sheet and see what happens
Excelchat Expert 12/03/2018 - 01:14
If you are using another spreadsheet, please add the references in the formula for index for the range as well very similar to match function that you have used.
User 12/03/2018 - 01:19
it not working for me
User 12/03/2018 - 01:19
can i set it up in this sheet then you show me again
Excelchat Expert 12/03/2018 - 01:19
Can you please copy the formula here?
User 12/03/2018 - 01:21
=INDEX(WORKSHEET!C8:C4522,MATCH(C11,WORKSHEET!C8:C4522,0),20)
User 12/03/2018 - 01:22
thats what i have for the formula in my sheet
Excelchat Expert 12/03/2018 - 01:23
Please use 1 instead of 20 at the end as you have range only for one column? But, why the index and match have same range?
Excelchat Expert 12/03/2018 - 01:23
WORKSHEET!C8:C4522
User 12/03/2018 - 01:24
didnt notice that ill change it and see what happens
Excelchat Expert 12/03/2018 - 01:24
For Index, you may have to use a different column. Can you please set up in the preview.
User 12/03/2018 - 01:28
in the preview i would need b8 to find value in a8 in sheet 2 then place the value that in b8 in sheet 2 in b8 in sheet 1
Excelchat Expert 12/03/2018 - 01:28
Ok. I will work on this.
Excelchat Expert 12/03/2018 - 01:29
=index(Sheet2!A8:B1000,match(A8,Sheet2!A8:A1000,0),2)
Excelchat Expert 12/03/2018 - 01:30
Or the formula can be written in another way also.
Excelchat Expert 12/03/2018 - 01:30
=index(Sheet2!B8:B1000,match(A8,Sheet2!A8:A1000,0),1)
Excelchat Expert 12/03/2018 - 01:31
The last number is based on the range you have mentioned after Index text.
User 12/03/2018 - 01:32
okay let me try this again in my sheet
User 12/03/2018 - 01:36
GOT IT!!!!
Excelchat Expert 12/03/2018 - 01:37
Great to hear that. That is an excellent news.
Excelchat Expert 12/03/2018 - 01:37
Thank you to use our services to solve your problem. Hopefully, you got better idea of the formula and you can apply it everywhere, when there is a requirement.
User 12/03/2018 - 01:38
it really is. but the last number i dont quite understand fully. i thought it was the column
Excelchat Expert 12/03/2018 - 01:39
It is based on the range you are selecting and the column you are referring in the range.
Excelchat Expert 12/03/2018 - 01:39
A:B asks for 2 as you need data from column B.
Excelchat Expert 12/03/2018 - 01:39
B:B asks for 1 as you need data from column B.
Excelchat Expert 12/03/2018 - 01:40
As the row is arrived using match. the only requirement in index is range and column number the value to be pulled from.
Excelchat Expert 12/03/2018 - 01:40
Easiest way is B:B and 1 as column.
Excelchat Expert 12/03/2018 - 01:41
But, if you want to make it generic formula, then you use Index($A:$C,row,2) for value from column B.
Excelchat Expert 12/03/2018 - 01:41
Index($A:$C,row,3) for the value from column C. So, a generic range can be applied for all the index formulas.
User 12/03/2018 - 01:44
so if i wouldve name the table array as the whole sheet 2 so my reference then becomes the whole sheet 2 thats when that number on the end changes to the column i want excel to pull the number from, right?
Excelchat Expert 12/03/2018 - 01:45
Yes. It is from the column you are referring from.
User 12/03/2018 - 01:45
got it, thank you very much for the help
Excelchat Expert 12/03/2018 - 01:45
if you need from column C and if you mention the range as B:C, then you will use 2 as the column number.
Excelchat Expert 12/03/2018 - 01:46
Thank you. Have a great day ahead.
User 12/03/2018 - 01:46
makes sense
User 12/03/2018 - 01:46
thank you you as well

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.