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.
