Question description:
This user has given permission to use the problem statement for this
blog.
VLOOKUP help. Trying to input data from database into new table
Solved by S. Q. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
27/09/2018 - 12:55
Hi
Excelchat Expert
27/09/2018 - 12:55
Hi, welcome to Got it Pro-Excel!
Excelchat Expert
27/09/2018 - 12:55
According to my diagnosis, you need help with VLOOKUP, is that correct?
User
27/09/2018 - 12:55
Im looking to input data from a database into an excel sheet based on common items
Excelchat Expert
27/09/2018 - 12:56
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows. We also currently do not support VBA/Macro solutions.
User
27/09/2018 - 12:56
ok
Excelchat Expert
27/09/2018 - 12:56
Let me ask you a couple of quick questions to make sure I fully understand your problem.
Excelchat Expert
27/09/2018 - 12:56
When you say database, do you mean a database outside Excel application?
User
27/09/2018 - 12:56
no, my own databse ive created in excel
[Uploaded an Excel file]
Excelchat Expert
27/09/2018 - 12:56
Oh ok, that's clear.
User
27/09/2018 - 12:57
this is the sheet id like to input information into
Excelchat Expert
27/09/2018 - 12:57
Give me a moment to review your file.
User
27/09/2018 - 12:57
heres my database
User
27/09/2018 - 12:57
give me a second
User
27/09/2018 - 12:57
[Uploaded an Excel file]
User
27/09/2018 - 12:58
This is the database that id like to extract information from
User
27/09/2018 - 12:58
Now.... what i need to do is input the cost of an item into the new sheet based on the pricing i have in the database for each specific item
Excelchat Expert
27/09/2018 - 12:59
Ok, give me a moment to review your files.
Excelchat Expert
27/09/2018 - 01:01
Please correct me if I'm wrong - You want the prices from the Pricing Database to be pulled into the 30 East Water St file, is that correct?
User
27/09/2018 - 01:01
yes
User
27/09/2018 - 01:01
into cells in Row D
Excelchat Expert
27/09/2018 - 01:02
So I'll replace your data in Column D at the moment?
User
27/09/2018 - 01:02
including what i already have in there
User
27/09/2018 - 01:02
if u scroll down, ive input about 100 items manually
User
27/09/2018 - 01:03
in Column D
User
27/09/2018 - 01:03
row 108
Excelchat Expert
27/09/2018 - 01:04
of the 30 East Water St?
User
27/09/2018 - 01:04
yes
User
27/09/2018 - 01:05
From D 108 Down, i need to input the price according to my database
Excelchat Expert
27/09/2018 - 01:05
Ok, I've checked 3 random items in here and none of them matches any items in your database.
User
27/09/2018 - 01:05
yes, its a working database
User
27/09/2018 - 01:06
id like to highlight ALL items, and IF they match in both sheets, then to pull the price
Excelchat Expert
27/09/2018 - 01:06
Oh ok. That makes sense.
Excelchat Expert
27/09/2018 - 01:07
Based on what you’ve shared, you need a vlookup formula to be applied in Column D of 30 East Water St file and when it matches an item from the database, pull the price and highlight the item, do you believe that will address your problem?
User
27/09/2018 - 01:07
yes, but i dont need to highlight the item
User
27/09/2018 - 01:07
just to pull the price
User
27/09/2018 - 01:08
need to know soon
Excelchat Expert
27/09/2018 - 01:08
Oh ok ok,
User
27/09/2018 - 01:08
the items highlighted are just duplicates in conditional formatting
Excelchat Expert
27/09/2018 - 01:08
I’m going to apply the vlookup formula for you. This should take me a couple of minutes to finish.
Excelchat Expert
27/09/2018 - 01:09
Just to confirm - I'm starting from row 108 up to the last cell?
User
27/09/2018 - 01:09
*DOWN to the last cell
User
27/09/2018 - 01:09
this isnt that difficult...
Excelchat Expert
27/09/2018 - 01:09
Yes, sorry for that mistake.
Excelchat Expert
27/09/2018 - 01:09
Yes, I just wanted to confirm.
Excelchat Expert
27/09/2018 - 01:12
Here's your file.
[Uploaded an Excel file]
Excelchat Expert
27/09/2018 - 01:13
VLOOKUP formula was applied to row 108 down to the last cell before the total.
User
27/09/2018 - 01:14
the file wont open?
Excelchat Expert
27/09/2018 - 01:14
Can you provide more information on why it won't open?
Excelchat Expert
27/09/2018 - 01:15
Would you like to apply the formula from your end if I walk you through it?
Excelchat Expert
27/09/2018 - 01:16
Also, since the vlookup is referencing a table from a separate file, There might be issues with the table reference.
Excelchat Expert
27/09/2018 - 01:17
Hi, are you still there?
Excelchat Expert
27/09/2018 - 01:18
I believe you got disconnected from our chat.
Excelchat Expert
27/09/2018 - 01:19
You can use this formula on Cell D108 of 30 East Water St
Excelchat Expert
27/09/2018 - 01:19
=IFERROR(VLOOKUP(C108,'[Pricing Database.xlsx]Sheet1'!$C:$D,2,0),"")
Excelchat Expert
27/09/2018 - 01:20
Make sure you have your pricing database file open as well.
Excelchat Expert
27/09/2018 - 01:20
That should answer your question.
Excelchat Expert
27/09/2018 - 01:20
To explain how VLOOKUP formula works so you can do it by yourself moving forward.
Excelchat Expert
27/09/2018 - 01:21
VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table.
Excelchat Expert
27/09/2018 - 01:21
The "V" stands for "vertical". Lookup values must appear in the first column of the table, with lookup columns to the right.
Excelchat Expert
27/09/2018 - 01:22
How it works: Lookup a value in a table by matching on the first column.
Excelchat Expert
27/09/2018 - 01:23
This is the syntax of VLOOKUP
Excelchat Expert
27/09/2018 - 01:23
=VLOOKUP (value, table, col_index, [range_lookup])
Excelchat Expert
27/09/2018 - 01:23
value - The value to look for in the first column of a table.
Excelchat Expert
27/09/2018 - 01:23
table - The table from which to retrieve a value.
Excelchat Expert
27/09/2018 - 01:23
col_index - The column in the table from which to retrieve a value.
Excelchat Expert
27/09/2018 - 01:23
range_lookup - [optional] TRUE = approximate match (default). FALSE = exact match.
Excelchat Expert
27/09/2018 - 01:24
To walk you through your actual formula.
Excelchat Expert
27/09/2018 - 01:24
VLOOKUP(C108,'[Pricing Database.xlsx]Sheet1'!$C:$D,2,0)
Excelchat Expert
27/09/2018 - 01:24
I'll use our shared sheet for visual.
Excelchat Expert
27/09/2018 - 01:26
If you'll look at column H in our shared sheet.
Excelchat Expert
27/09/2018 - 01:26
It's a breakdown of the formula that I used in your file.
Excelchat Expert
27/09/2018 - 01:27
The value that the function will be looking for is cell C108 of your 30 East Water St file.
Excelchat Expert
27/09/2018 - 01:28
And since your item name and price of your Pricing Database file is in column C and D that will be your table to lookup from.
Excelchat Expert
27/09/2018 - 01:28
Remember, the value that you're looking for must be in the first column of the table. Otherwise, vlookup won't work.
Excelchat Expert
27/09/2018 - 01:29
col_index is the number of column from the first column of your table.
Excelchat Expert
27/09/2018 - 01:30
Since your table is Columns C:D of Pricing Database file and you want to return the price which is placed in Column D of the Pricing Database file - it will be the 2nd column.
Excelchat Expert
27/09/2018 - 01:30
C=1, D=2.
Excelchat Expert
27/09/2018 - 01:30
Last will be the range_lookup.
Excelchat Expert
27/09/2018 - 01:31
This is optional but by default it's set as TRUE - and you don't want that. We'll always need FALSE which should lookup the exact match.
Excelchat Expert
27/09/2018 - 01:31
range_lookup only accepts TRUE or FALSE but since 0 is also equals to FALSE and 1 is equal to TRUE. We can also use 1 and 0.
Excelchat Expert
27/09/2018 - 01:32
I believe that should assist you when you read this chat from your history.
Excelchat Expert
27/09/2018 - 01:32
It's unfortunate that you got disconnected.
Excelchat Expert
27/09/2018 - 01:32
I'd like to wish you have a very nice day ahead of you and we'd love to hear from you again. Thank you for using Got it Pro-Excel!
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.
