Page 1 of 1

[Dropped] Look up data based on cell value, then multiply

PostPosted: Thu Apr 29, 2021 6:52 pm
by Flikks
Hello,i have a problem im too stupid to solve myself, i want to make a sheet that Takes a price out of column P when i type in the product name from column O and multiplicate it with a number in the same row from Column D.

Any help would be appreciated thank you in advance

Re: Questions about the applying of data validation

PostPosted: Thu Apr 29, 2021 7:18 pm
by MrProgrammer
Hi, and welcome to the forum.

Flikks wrote:Takes a price out of column P when i type in the product name from column O
[Tutorial] VLOOKUP questions and answers

Flikks wrote:multiplicate it with a number in the same row from Column D
Read section 8. Using formulas and cell references in Ten concepts that every Calc user should know.
Perhaps read [Tutorial] How do I specify the formula for a column?.

To receive any additional assistance from me you must attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

Re: Questions about the applying of data validation

PostPosted: Thu Apr 29, 2021 8:12 pm
by Flikks
thank you for the tutorials, however i still dont fully understand how to setup the formula.
I gonna reiterate my problem again:
i have a list of product names in one column and the corresponding pricelist in the next column, and i want to have a a setup where the correct price will show in column three when i type in the name of the product in column 1 and the quantity in column 2.

i tried to follow the tutorial, this is my (not working) formula =VLOOKUP(C21;O15:P28;3;0)
C21 is the row where i type in the name of the product, O15-P28 is the search table (o are the names and P are the prices)

Thank you for your help :).

Re: Questions about the applying of data validation

PostPosted: Thu Apr 29, 2021 9:11 pm
by Villeroy
=VLOOKUP(C21;O15:P28;3;0) applied to cell D21 looks up the left neighbour C21 in the range 11 columns to the right (D to O) and 5 rows higher (21 to 15) until 12 columns to the right (D to P) and 7 rows down (21 to 28).
When you copy down this formula one cell down to D22
=VLOOKUP(C22;O16:P29;3;0) still looks up the left neighbour in the range 11 columns to the right and 5 rows higher until 12 columns to the right and 7 rows down.

To make things worse, O15:P28 includes 2 columns and you try to lookup column #3 of 2 columns which raises an error anyway.

Re: Questions about the applying of data validation

PostPosted: Fri Apr 30, 2021 5:40 pm
by A.Fish
Flikks wrote: i tried to follow the tutorial, this is my (not working) formula =VLOOKUP(C21;O15:P28;3;0)
C21 is the row where i type in the name of the product, O15-P28 is the search table (o are the names and P are the prices)


The formula you have (minus the 3rd column error) will only return the dollar amount you have in column P for that product, you will then need to multiply that by the number of that product (your column B) to get the result you are looking for to show in column C.

One thing Villeroy is hinting at is to not forget and use your dollar signs ' $ ' to hold your search table to an absolute range, (ie $O$15:$P$28) otherwise as you drag your formula down the sheet, the range of cells being searched will also change which will cause unexpected results. If you expect the search table to expand/change or you have a lot of products you may also want to consider putting it on a separate sheet.