Excel - How to Use a VLOOKUP Formula - Expert Solution

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

So I have used vlookup to find the price of a product on a different sheet and then I have tried to times that by a quantity. e.g. =vlookup(d4,table35,2,false)*e4 This is the formula I typed in which worked for another list I did but for some reason it's not working now even though everything is practically the same, apart from the contents. Could you help?
Solved by B. U. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 03/08/2018 - 03:43
hello there
Excelchat Expert 03/08/2018 - 03:43
Can you please share the file you are facing problem with
User 03/08/2018 - 03:44
hi, i cannot as this is on a different computor
Excelchat Expert 03/08/2018 - 03:44
That will be difficult then to resolve the issue
Excelchat Expert 03/08/2018 - 03:45
what is the error you are facing?
User 03/08/2018 - 03:47
i have used vlookup to find the price of something from a drop down list with the source being on a different sheet. i have then tried to times the price by a quantity so i added *e4 on to the end of the vlookup formula but it says #vALUE!
User 03/08/2018 - 03:47
i have already done this on another list and it worked fine so i dont know wha the problem is
Excelchat Expert 03/08/2018 - 03:50
check the data type of the searched value
Excelchat Expert 03/08/2018 - 03:50
check if your data to match and data of the table is of same format?
Excelchat Expert 03/08/2018 - 03:52
so are they?
User 03/08/2018 - 03:52
yes they are all formatted as acounting
Excelchat Expert 03/08/2018 - 03:53
vlookup(d4,table35,2,false).....d4 is accounting too?
User 03/08/2018 - 03:54
no d4 is the drop down list of products its format is genral
Excelchat Expert 03/08/2018 - 03:54
I think thats why the matching is not ok, try to change d4' format
Excelchat Expert 03/08/2018 - 03:55
to accountinng
User 03/08/2018 - 03:55
User 03/08/2018 - 03:56
it hasnt changed it
Excelchat Expert 03/08/2018 - 03:56
then another two thing can happen
Excelchat Expert 03/08/2018 - 03:56
the table is not responding may be
Excelchat Expert 03/08/2018 - 03:57
what is the range of the table?
User 03/08/2018 - 03:57
a17 to b32
Excelchat Expert 03/08/2018 - 03:58
try putting it as $a$17:$b$32 instead of the table
User 03/08/2018 - 03:59
do i need to put what sheet it is on?
User 03/08/2018 - 03:59
the table is on sheet 1 a
Excelchat Expert 03/08/2018 - 04:00
and you are looking in different sheet?
User 03/08/2018 - 04:01
yes, im working on sheet 2 which is where i want to choose a product times it by a quantity and get the total price but all the information (the products and prices) are on tables on sheet 1
Excelchat Expert 03/08/2018 - 04:03
in that case write as Sheet1!$A$17:$B$32
User 03/08/2018 - 04:05
its still has the #VALUE!
Excelchat Expert 03/08/2018 - 04:05
can you send me a screenshot of the sheets?
User 03/08/2018 - 04:06
yes hold on a sec
Excelchat Expert 03/08/2018 - 04:10
you there?
User 03/08/2018 - 04:10
yes sorry i have to email it to this computer first
Excelchat Expert 03/08/2018 - 04:11
sure, in that case it will be better if you can share the excel sheet
User 03/08/2018 - 04:13
how do you do that?
Excelchat Expert 03/08/2018 - 04:14
there is a clip symbol, press that to attach file
User 03/08/2018 - 04:15
but my excel spread sheet is not on this computer, its on a different one
Excelchat Expert 03/08/2018 - 04:15
ah, you were saying to email may be?
User 03/08/2018 - 04:17
[Uploaded an Excel file]
User 03/08/2018 - 04:17
soz that should be sheet 1
User 03/08/2018 - 04:17
and this should be sheet 2
[Uploaded an Excel file]
User 03/08/2018 - 04:18
sheet 1?
[Uploaded an Excel file]
Excelchat Expert 03/08/2018 - 04:20
can you send me the picture with mouse over col A in sheet 1 and mouse over col D for sheet 2
User 03/08/2018 - 04:21
with the whole column selected?
Excelchat Expert 03/08/2018 - 04:22
no just any cell with data, also on column B data of sheet1
User 03/08/2018 - 04:23
ah this is a lot of work, it was just for my nans shopping list but i think she wants to give up :p thank you for your help
Excelchat Expert 03/08/2018 - 04:24
If you can any how send me the file
Excelchat Expert 03/08/2018 - 04:24
I can make it work
User 03/08/2018 - 04:24
okay i will screen shot again
User 03/08/2018 - 04:31
sheet1 column b is just all the prices which have been put in manually, so it doesnt have any formulas or anything, this should be the screen shot of column d sheet 2
[Uploaded an Excel file]
User 03/08/2018 - 04:31
does that help?
Excelchat Expert 03/08/2018 - 04:36
Excelchat Expert 03/08/2018 - 04:37
put the above formula in D4 sheet 2
Excelchat Expert 03/08/2018 - 04:38
in F4..not D4
Excelchat Expert 03/08/2018 - 04:38
User 03/08/2018 - 04:40
a warning sign came up saying the formula i typed contains an error
Excelchat Expert 03/08/2018 - 04:41
write down what you have put please

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.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc