Excel - Help on VLOOKUP Problem - Expert Solution

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.

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.