look up value from contents of list

English
, ,
#1

I have a really simple requirement but nothing I’ve tried works.

I have a list of stock items on one sheet, with their unique descriptions and - via a few calculations - their sale prices.

Let’s say the sheet is called ‘Costing’, descriptions are in cells A2 - A12 and the calculated sale prices in cells H2 - H12. The value in H2 corresponds to the description in A2.

On another sheet called ‘Orders’ I’ve set up a drop-down list on this sheet in C2 (using the cell range A2 - A12), and depending on which value is selected I’d like the corresponding value from H2 - H12 to appear in cell D2.

The only function which gives me anything other than error codes is VLOOKUP - but the results are nonsense. Example: =VLOOKUP(C2,$‘Costing’.A2:I12,9,0)

This feels like it should be really simple, but I am stumped!

#2

Hi, are you able to upload a sample of you file?

#3

=VLOOKUP(C2;$'Costing'.A2:I12;8;0)
H is the eighth column if the range starts with column A.
BTW: Better use the semicolon as the “function” separator. It doesn’t get in conflict with internationally used decimal separators. You can set this separator under
>Tools>Options>LibreOffice Calc>Formula>Separators.

#4

If do you need more than one row with this formula, replace A2:I12 with A$2:I$12, then fill down or copy and paste.

#5

@Lupp: your sample works, make it an answer? my ver. 7.1 automatically removes the quotes around ‘costing’, don’t know when, if and why they are useful or neccessary …

#6

@newbie-02:
2) The single apostrophes are needed for disambiguation if the chosen “name” of the sheet isn’t a syntactically acceptable name. Directly entered, the apostrophes are accepted first, but then removed automatically if not needed. It’s probably different in Excel, and users coming from there may tend to always type the apostrophes.

  1. There are lots of answers concerning the usage of VLOOKUP(). I personally don’t like to stress that function, because I’m under the impression that it’s often used in a very inefficient way. In addition, its fourth parameter is used in a different way as compared to the MatchType parameter of MATCH() - and few users may be ready to study this in detail before using the function. (In the current case that’s not relevant since 0 is placed there.)
  2. You are free, of course, to answer the question yourself (and to use my example if you want.)
#7

@Lupp:
2) thanks, profound knowledge,

  1. ‘different way’ - things like this happen, i once criticized RC and CR naming convention mixed up in some function … got a slap “it’s like this and is correct”,
  2. tried that, not answering myself but referencing your comment in the answer, got a downvote, deleted the answer, deducted karma stays … :frowning:
    not so easy to learn all things to care for …
#8

Don’t care too much. And, first of all, don’t worry.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.