Why is VLOOKUP returning inaccurate results in CALC?

[mlr27]

Hello,

I’m trying to get my sales spreadsheet to auto-fill the correct “material cost” when I select a sold item from a dropdown menu. To achieve this I’m using the following formula in the cost cell G4:

*=IFERROR((VLOOKUP(B4,$Sheet2.$A$5:$Sheet2.$E$54,5)C4),0)

My understanding of what this should achieve:

• “IFERROR(…,0)” returns a numerical
  value of 0 instead of N/A
• “VLOOKUP(B4,…” identifies B4 as the cell containing a dropdown menu of my products
• “$Sheet2.$A$5:$Sheet2.$E$54” identifies the full dataset of products fleshing out that dropdown menu, i.e. a range of cells (50 rows, 5 columns) on another sheet that lists my products
• “,5)” instructs G4 to identify the corresponding figure in column E of the product dataset
• “*C4)” multiplies that figure by the number in C4 (which is the number of units sold)

So an example of what I want to happen: I choose “Wyvern meadows” from the dropdown menu in B4; VLOOKUP in G4 refers back to sheet 2, finds “Wyvern meadows” in row 8, identifies a corresponding material cost of £0.69237 in column E; that figure is then multiplied by 1, because one unit was sold; and so G4 is automatically filled with £0.69237.

However, that isn’t happening. See my spreadsheet here: REMOVED You can see that the column in question (Sheet 3, column G, highlighted in yellow) returns a chaotic jumble of results. Sometimes it returns the correct figure, but just as often it gets it wrong.

Can anyone please help? I’ve used VLOOKUP in a similar way in columns J & L without problem, so I can’t understand why column G is behaving so differently!

Thanks in advance

[JohnSUN]

Welcome! Your reasoning is absolutely correct. You have left out only one feature of VLOOKUP() - there is a fourth optional parameter SortedRangeLookup. Just specify it as 0.

[karolus]

please read the Help-page for VLOOKUP

[karolus]

Hallo
asl ong your Produkt-Names in Sheet2 are not sorted, you have to use the 4th optional Argument 0 inside the VLOOKUP

=IFERROR( VLOOKUP( B4 , $Sheet2.$A$5:$E$54 , 5 , 0 )*C4,"")

[anon73440385]

Hello,

: I choose “Wyvern meadows” from the dropdown menu in B4;

There is no “Wyvern meadows” (exactly) in drop down list. And here your misunderstanding may start: You omitted the fourth parameter in function VLOOKUP() and VLOOKUP()to work correctly, range $Sheet2.$A$5:$Sheet2.$E$54 must be sorted, which it isn’t.

Ref.: LibreOffice Help - VLOOKUP - Section SortedRangeLookup

Additional advice

Use a separate column in Sheet2 to categorize your products (SOAP; SNAPWAX; …) instead of using pseudo-header in the same column

[mlr27]

Thank you so much JohnSUN, Opaque & Karolus! How nice to have an easy fix like that. And I now also see that when I modify product names in the original dataset (as I did for Wyvern) that will confuse VLOOKUP and mess up some of my historic sales costings. (Is there a way to make the amended name retrospectively applied to the drop-down entries on Sheet 3 that have already been filled in? Doesn’t matter if not - I can just be careful.) Thanks again!

[keme]

This is not a solution, but a “supplemental question” of sorts. In this place, post an answer only for suggestions towards a solution. For additional info to your issue, edit your question or post a comment. For new questions, start a new entry.

This one is “borderline” supplememtal/new question, I guess. Since you ask with an answer, I’ll suggest an answer as a comment. It’s a mess, and we will share the blame…

There is no easy way to have source changes “fold back” to the target (validated) records. Data validation occurs at entry time, and data is then entered in cell verbatim.

You might write a macro for this, but the macro would have to consider possible duplicates in validation data and keep constant track of all validated cells. The potential for error is significant.

In a proper database, the solution to this is more or less a trivial linking of tables.