Excel - How to Use a VLOOKUP Function - Expert Solution

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

HOW TO USE VLOOKUP FOR ENTIRE SHEETWORK AND MULTIPLE TABS?
Solved by T. Y. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 31/05/2018 - 01:09
Hi welcome!
Excelchat Expert 31/05/2018 - 01:10
You have a question on Vlookup use on multiple sheets?
User 31/05/2018 - 01:10
Hi
User 31/05/2018 - 01:10
yes
User 31/05/2018 - 01:10
My project is for reporting
Excelchat Expert 31/05/2018 - 01:11
There are no differences in the syntax of the VLOOKUP formula for other sheets.
Excelchat Expert 31/05/2018 - 01:11
We just need to make sure the limitations of VLOOKUP are not getting in the way.
Excelchat Expert 31/05/2018 - 01:11
Do you have a sheet you can upload?
User 31/05/2018 - 01:13
no, because it confidential
Excelchat Expert 31/05/2018 - 01:13
Or an example with dummy data?
User 31/05/2018 - 01:14
i have multiple sheets like this with different data
User 31/05/2018 - 01:17
can you see what i am pasting in excel now
Excelchat Expert 31/05/2018 - 01:17
ok, I can try to add some dummy data
Excelchat Expert 31/05/2018 - 01:17
No, I was pasting
Excelchat Expert 31/05/2018 - 01:17
What sheet?
User 31/05/2018 - 01:17
1
Excelchat Expert 31/05/2018 - 01:17
ok
Excelchat Expert 31/05/2018 - 01:18
and sheet 2
User 31/05/2018 - 01:18
yes
Excelchat Expert 31/05/2018 - 01:18
and 3
User 31/05/2018 - 01:18
yes
Excelchat Expert 31/05/2018 - 01:19
So where is the formula -sheet 4?
Excelchat Expert 31/05/2018 - 01:20
Ok, what information do you need from each sheet?
Excelchat Expert 31/05/2018 - 01:21
For VLOOKUP to work , regardless of sheet count, the KEY field must be on the far left.
Excelchat Expert 31/05/2018 - 01:21
VLOOKUP only goes right--->
Excelchat Expert 31/05/2018 - 01:22
right accross columns
Excelchat Expert 31/05/2018 - 01:22
HLOOKUP goes down rows.
User 31/05/2018 - 01:22
oky
Excelchat Expert 31/05/2018 - 01:23
IN this setup easy to VLOOKUP using ITEM
Excelchat Expert 31/05/2018 - 01:24
Sheet does not matter. A least in EXCEL, not always 100% sure on this view screen.
User 31/05/2018 - 01:25
i want every count that i do for every month transfer to another worksheet relating to the excat item
Excelchat Expert 31/05/2018 - 01:25
every count means?
Excelchat Expert 31/05/2018 - 01:26
Item is column c?
User 31/05/2018 - 01:26
no b
Excelchat Expert 31/05/2018 - 01:27
B is all same value
Excelchat Expert 31/05/2018 - 01:27
you mean count how many = APOLLO?
User 31/05/2018 - 01:27
yes
User 31/05/2018 - 01:28
go to sheet 2
Excelchat Expert 31/05/2018 - 01:28
Yes, see 3 in column B = APOLLO
User 31/05/2018 - 01:28
we have the Apollo in different colour
Excelchat Expert 31/05/2018 - 01:29
Column C
Excelchat Expert 31/05/2018 - 01:29
So just count column b or ??
User 31/05/2018 - 01:30
i want total for all the 3 colorus to be tansfered to another work sheet that tells my boss how many we have sold in a month
User 31/05/2018 - 01:31
which is lighlighted in red
Excelchat Expert 31/05/2018 - 01:31
I think you just need a PIVOT table
Excelchat Expert 31/05/2018 - 01:32
Count or ADD values in a table base on attributes Like APOLLO and COLOR.
User 31/05/2018 - 01:33
how ?
Excelchat Expert 31/05/2018 - 01:34
You mean how with many sheets or just a single PIVOT?
User 31/05/2018 - 01:35
Many sheets please
Excelchat Expert 31/05/2018 - 01:36
Ok, that is way beyond the simple VLOOKUP question posted.
Excelchat Expert 31/05/2018 - 01:37
and can't be done in this viewer-advanced PIVOT functions required.
Excelchat Expert 31/05/2018 - 01:37
I can take these 3 sheets and work in EXCEL off-line and have site support send to you .
Excelchat Expert 31/05/2018 - 01:38
But given this is just sample data , it may be hard for you to replicate.
Excelchat Expert 31/05/2018 - 01:38
Are you familar with PIVOTS?
User 31/05/2018 - 01:38
yes
Excelchat Expert 31/05/2018 - 01:39
Ok, then you get the issue. You want a cross tab report accross mutliple sheets and then PIVOT.
Excelchat Expert 31/05/2018 - 01:39
Other option , I think may work here. is if all sheets are structured the same , consolidate into 1 summary sheet and then PIVOT.
User 31/05/2018 - 01:40
Thank for your help can i speak to you later
Excelchat Expert 31/05/2018 - 01:40
Either way some work to be done. and I would have to do that off-line.
User 31/05/2018 - 01:40
Are you in Australia
Excelchat Expert 31/05/2018 - 01:40
Yes, try the consolidate then PIVOT
Excelchat Expert 31/05/2018 - 01:41
No i am in the US. I think I am allowed to say that.
User 31/05/2018 - 01:41
oh i was about to ask you if youy do coruses for excel
User 31/05/2018 - 01:42
courses ***
Excelchat Expert 31/05/2018 - 01:42
I just support this chat line which is student 1 question formula stuff. You can go to various FREELANCE sites for more advanced and lengthy support.
User 31/05/2018 - 01:44
look i will be back
Excelchat Expert 31/05/2018 - 01:44
You can re-post your question and I am confident anyone on this line can assist you.
Excelchat Expert 31/05/2018 - 01:44
Ok, have a great day there!

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.