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
ok
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
her
[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
Vlookup(D4,Sheet1!$A$17:$B$32,2,0)
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
done?
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.
