Excel - General Question on Pivot Table - Expert Solution

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

need to learn pivot table v look up and h look up
Solved by B. B. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 08/08/2017 - 08:44
Hi there, how are you?
Excelchat Expert 08/08/2017 - 08:44
Do you have a sample data we can work on?
Excelchat Expert 08/08/2017 - 08:46
Hello
User 08/08/2017 - 08:46
hellow sir
User 08/08/2017 - 08:47
i have no file sir
Excelchat Expert 08/08/2017 - 08:47
No worries. I'll get a file we can work on
Excelchat Expert 08/08/2017 - 08:47
One moment please
User 08/08/2017 - 08:48
thanks sir
Excelchat Expert 08/08/2017 - 08:48
We start with VLooup first
Excelchat Expert 08/08/2017 - 08:49
Do you see the table I created on the left?
User 08/08/2017 - 08:50
yes sir
Excelchat Expert 08/08/2017 - 08:50
This is normally what a data would look like, header names with values below it.
User 08/08/2017 - 08:50
ok got it
Excelchat Expert 08/08/2017 - 08:50
Great. Now I created 3 headers on the right side.
User 08/08/2017 - 08:50
ok
Excelchat Expert 08/08/2017 - 08:51
For Vlookup, the Excel syntax is : VLOOKUP(search_key, range, index, [is_sorted])
Excelchat Expert 08/08/2017 - 08:51
Let me show that on the sheet
User 08/08/2017 - 08:51
its formula
Excelchat Expert 08/08/2017 - 08:51
Yes correct. Formula
User 08/08/2017 - 08:52
ok
Excelchat Expert 08/08/2017 - 08:52
So for example, we need to get the Name and Age of a certain ID
User 08/08/2017 - 08:52
ok
Excelchat Expert 08/08/2017 - 08:53
In the formula I created, the first parameter will always be the main value we will use for the Lookup
User 08/08/2017 - 08:53
ok got its sir / medam
Excelchat Expert 08/08/2017 - 08:53
in this case. It is the ID number. with the value of 3333
Excelchat Expert 08/08/2017 - 08:54
Second part of the syntax is the range of data we will use in our lookup
Excelchat Expert 08/08/2017 - 08:54
which is range A2 to range C3
Excelchat Expert 08/08/2017 - 08:54
third parameter is the column Number. so for Name, that will be column #2
Excelchat Expert 08/08/2017 - 08:55
and lastly we use 0 in the last parameter, to get the exact match of the data we are trying to lookup.
Excelchat Expert 08/08/2017 - 08:55
Oh I see what went wrong.
Excelchat Expert 08/08/2017 - 08:55
the Range adjusted.
Excelchat Expert 08/08/2017 - 08:55
should still be A3:C11
Excelchat Expert 08/08/2017 - 08:55
in your column G formula
User 08/08/2017 - 08:56
sorry
Excelchat Expert 08/08/2017 - 08:56
one thing to avoid that is to lock the formula, make it absolute. you can do that by highlighted that particular part of the formula then press F4 on your keyboard
User 08/08/2017 - 08:57
ok thanks
Excelchat Expert 08/08/2017 - 08:57
like this
Excelchat Expert 08/08/2017 - 08:57
so even if you copy the formula to other cells. it won't change
User 08/08/2017 - 08:57
ok
User 08/08/2017 - 08:57
may i know your good name ?
Excelchat Expert 08/08/2017 - 08:58
You may refer to me as Exp_XYZ
Excelchat Expert 08/08/2017 - 08:58
:)
Excelchat Expert 08/08/2017 - 08:59
Now for the HLOOKUP
User 08/08/2017 - 08:59
ok
User 08/08/2017 - 09:01
fast sir / medam
User 08/08/2017 - 09:01
session wiil expire
User 08/08/2017 - 09:01
soon
Excelchat Expert 08/08/2017 - 09:02
Hi
User 08/08/2017 - 09:02
hi
Excelchat Expert 08/08/2017 - 09:02
Yes, in case this session ends.
Excelchat Expert 08/08/2017 - 09:02
Please click this link: https://docs.google.com/spreadsheets/d/1k0Xb1IcC-QNXIX9eA3Vvs11Ty_wDU8qgMtuT4B8OUpE/edit#gid=0
Excelchat Expert 08/08/2017 - 09:02
We can continue our session there for 20 minutes more.
User 08/08/2017 - 09:02
ok
Excelchat Expert 08/08/2017 - 09:02
Alrighjt. I have the data for HLOOKUP example
User 08/08/2017 - 09:03
nice
Excelchat Expert 08/08/2017 - 09:03
This is pretty much similar to VLOOKUP. Instead we do it Horizontally
User 08/08/2017 - 09:03
ok got it
Excelchat Expert 08/08/2017 - 09:03
There you go
Excelchat Expert 08/08/2017 - 09:03
By the way, please click on the link now
Excelchat Expert 08/08/2017 - 09:04
Session is about to end
Excelchat Expert 08/08/2017 - 09:04
https://docs.google.com/spreadsheets/d/1k0Xb1IcC-QNXIX9eA3Vvs11Ty_wDU8qgMtuT4B8OUpE/edit#gid=0

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.