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.
