LOOKUP use / problem

English
#1

Hi!
My problem is handling the LOOKUP instruction.
From my example
My desire is to find the value from cells in column F in column A and display the contents of column D in column I. the same desire in column G and show the result in column J
The instruction in I2 looks as follows: =LOOKUP(F2; A$2:A$10; $D$2:$D$10)
In the cell I2 it works, but not in I3
If the value in column I or J (in this case) dosen’t exist I got the error #N/A or something else.
Is it important that the cells have the same format?
Is it important that columns A and/or I are sorted?
I don’t like the message #N/A.
Are the correct instructions being used?
What should the solution look like?

Lookup example.ods (11.6 KB)

#2

The documentation would suggest this is the case.

But maybe you’ll like the function ISNA()

#3

Since 45 years (Visicalc v1), LOOKUP does not do what most users expect it to do. LOOKUP looks up categories by numeric scales:

POINTS GRADE
10 F
23 E
33 D
40 C
48 B
55 A

27 → E
40 → C
99 → A
9 → #N/A

LOOKUP can not look up database items. MATCH and VLOOKUP do the same scale lookup by default, unless you force it into “database mode” with the last argument explicitly set to False.

3 Likes
#4

(I’m not sure, how to use this forum - now I want to reply - I hope it works :slight_smile: )
Is my desire impossible to accomplish?
A slightly updated version of my example file is attached.
Lookup example v2.ods (12.3 KB)
Columns A - D have an ascending sort based on column A, and contain the base data.
Column F (not sorted) contains values ​​that (maybe) exist in column A (sorted) and
Column G (not sorted) contains values ​​that (maybe) exist in column B (not sorted)
In Cell I2 is a formula → If the value F2 is found in column A I want to see the value in column D
From my example calc
F2 is 0123 (and is missing in column A) but the contents from cell D2 are displayed.
F3 is in column A (A5) - cell D5 has no content - the result is not “empty” but #N/A
In columns L and M I try to use MATCH - with almost equally puzzling results.
Is there any other instruction I can use to solve my request? or …
.

#5

I have tried using the following commands with similar results
=VLOOKUP(F2; A2:D6; 4; “FALSE”) or =VLOOKUP(F2; A2:D6; 4; 0)

#6

A5 is a cell formatted as ‘text’ with a number in it, so it is considered a string.
F3 is a cell formatted as ‘number’ with left align, content is a number.

#7

No, but you have run into one of the typical problems of spreadsheets (and programming languages without strong types). You are allowed to put any type in a column. The “format” is only an instruction how to present the data, if the type is fitting the format.
When mixing types (often when you copy from other sources) you get problems when comparing to other data because the number 5 is not the same as a string “5” (maybe shown as '5 in Calc).
This is a point, where databases shine, as they enforce the type you have set.)
.
So you need to cleanup your data. (The function “text in columns” from the menu data may help here.)
.
Other hints:

  • Use absolute ranges A$2:D$10, as you already did in your fist post. Otherwise you can not drag down your formula.
  • You can’t avoid n/a for not found, but you can wrap your result with IFNA()-function.
#8

This is true. (often encounter this problem)
Don’t know how to handle this “problem” in a simple way.

  • Highlighting the column and selecting format/cells/text rarely helps (I don’t know why)
  • Sometimes the instruction =CONCATENATE(A5;"") works to convert from numbers to text (but not always)
  • The function =TEXT(A2;"@") does not work at all in this case.
  • The function =VALUE(A2) Always converts numeric text to numbers and gives an error message when it doesn’t work.
    In other words, I haven’t found a good way to convert numeric fields to text fields.
    Is it possible?
#9

Have now updated my spreadsheet.

  • Tried to make all cells as TEXT
  • Now there are formulas for
    - LOOKUP
    - MATCH
    - VLOOKUP
    (but ​​the desired result is not achieved)
    Lookup example v3.ods (12.5 KB)
#10

Hallo
B3;F3;G3;G4 are still numeric!

#11

The formatting of a cell (color, font, border, number format) must never change the value. Without the knowledge of fundamental spreadsheet concepts (same since 45 years), it is extremely difficult (if not impossible) to mimic a database on sheets.

#12

[quote=“karolus, post:10, topic:117983, full:true”]
B3;F3;G3;G4 are still numeric!
[/quote] (excuse me - only B3 in version 3 - but it doesn’t matter - I got the same result.

In other words, my wish is impossible to solve with Libre Office.

I have no idea how this could be solved with a database.
It would probably take me a day (or more) to create a relational database with my data.
The basis of my desire lies in two different CSV files (fields separated by ; - semicolon)
I have no idea how the data from these CSV files can be read/imported into Base.
After that, it could take just as long to create a report to solve my question. (I have no idea how)

If I sit for 3-4 hours and “manually” compare and search, it goes much faster. (Because Libre Office can’t help me with the “search”)

#13

A huge amount of Calc related problems (10% :question: )comes from inexperienced users trying to evaluate database data (all csv comes from databases) with an arithmetic calculator. First, they ignore the import options and import wrong data, and after they learned the hard way how to import data, it turned out that they never learned how to use a spreadsheet.
The one and only thing I would describe as “impossible” is this: Without concrete data samples of your csv, it is impossible to give any advice on how to import data correctly.
Then you really need some good old book. Any old Excel book of the 90ies will teach you how to use a spreadsheet like Calc. Same old song since decades.

#14

Thanks for all the help (even if this didn’t solve my problem)
My wish probably won’t come again (for the foreseeable future)

If I understand correctly, Lookup, vLookup and Match doesn’t work in Calc. (I still don’t understand why though). If a CSV file is not created from my programs, is it possible to create a working ods file (because I don’t know how to create a database table - from my programs).
I have no doubt that you (Villeroy) would be able to solve my request. (probably with a solution that I wouldn’t be able to understand)
I’ve tried creating tables and then trying to “import” CSV data, but there are a lot of steps before it starts working. (I never found the solution, though)
CSV-file 1 is column A-D and CSV-file 2 is column F and G.

#15

Lookup example v4.ods (12.9 KB)
Works as expected. I removed all silly formatting and the LOOKUP formulas, turned ID numbers into numeric text.

J2: =MATCH(F2; A$2:A$10; 0) => #N/A because F2 is “0123” which does not exist in A1:A10
J3: =MATCH(F3; A$2:A$10; 0) => 4 because F3 is “62227” which exists in A1:A10 @ position 4.
J4: =MATCH(F4; A$2:A$10; 0) => #N/A because F4 is empty.

K2: =MATCH($G$2; $B2:$B10; 0) => 3 because G2 is “64117” which exists in A1:A10 @ position 3.
K3: =MATCH(G3; B$2:B$10; 0) => 2 because G3 is “12345” which exists in A1:A10 @ position 2.
K4: =MATCH(G4; B$2:B$10; 0) => 1 because G4 is “7391” which exists in A1:A10 @ position 1.

#16

Thanks! (it´s a solution)
I see that there were several “errors” in my approach (in this example)
Since the example file was created directly in the spreadsheet, many formatting problems occurred. (sometimes a bit tricky to keep the cell as a text field)

Would be interesting to see what a solution with Base would look like.
How long it would take to use Base instead.

To eliminate errors/problems in Calc, I think the following steps should be a guide. (maybe :smile: )
1) Create CSV file1 in textformat - UTF-8 - filename vLookup1.csv - with the following format .:

"Col1";"Col2";"Col3";"Col4"
"00061";"7391";"Item 1";"Info 1!"
"456-32";12345;"Item 5";""
"61791";"64117";"Item 3";"Info 2!"
"62227";"4740";"Item 2";""
"AB123";"065A";"Item 4";"Some info"

vLookup1.zip (273 Bytes)

2) Open the csv file with the following prerequisites in Calc

  • Character set as .: UTF-8
  • Separation Character .: Semicolon
  • Format quoted field as text
  • Select all fields and set them as “text” (to be sure)

The information is now in columns A-D

3) (not sure if this is necessary)

  • Select DATA / Autofilter (first row as column header)
  • Sort “Col1” ascending

4) Save the file as vLookup example v1.ods

Do the same with vLookup2.csv

"Tst1";"Tst2"
"0123";"64117"
"62227";"12345"
"";"7391"

Copy the contents of vLookup2 (columns A and B) to columns F and G in the file vLookup example v1.ods

Create the formulas and add some formatting for better overview
In my case, the result was the following:
vLookup example v1.ods (12.9 KB)

Summary

  • If I had started right, maybe my desire would have been solved in 5 minutes.
  • Still unsure if the sorting of the contents of the CSV files is important for the function.
  • It would have been interesting to see a solution with Base and compare the pros and cons.
#17

The sort order is not that relevant when switching to “database mode” which looks up one particular item. This is what virtually everybody wants to do, disregarding the fact that this is not what spreadsheets do by default. In database mode, it matches at the first occurrence where the search value exists in the search column. In case of no exact match, the function returns #N/A.
If your search value occurs more than one time in the search column, the sort order may have some relevance, because you may get another item depending on which one of the duplicates is on top.

The sort order matters in “spreadsheet mode”. If the search value is smaller than the first value on the scale, it returns #N/A. Any other number gives some match, even if it does not exist in the list. Spreadsheet lookups match at the last position where the scale value is equal to or smaller than the search value.
Scroll up to my LOOKUP sample with points and grades. This table is sorted by points. When you look up any number, say 37, the function walks down the scale until it meets either one of

  1. exact match → this is it
  2. the end of the list → this is it
  3. a greater number → go back one position. If the greater number is at position 1, return #N/A.

In case of search value 37, the function walks down until 40 and goes back one position. That is 33 or grade D.

If the search column is not ordered ascendingly, the function may return #N/A because a value bigger than the search value is on top and valid matches follow. It may return a wrong match because it finds some value bigger than the search value, while closer matches could be found after that bigger value.

1 Like
#18

thank you!

When is Calc in “database mode” or “spreadsheet mode”? I just type a formula…
(in my example, columns A and B only have unique values)

Thanks for an explanation of the search table (never seen it before)

#19

And the last parameter decides how vlookup works, as Villeroy already wrote:

You may also use the help files or wiki to fin out what functions you use:
https://wiki.documentfoundation.org/Documentation/Calc_Functions/VLOOKUP

1 Like
#20

Spreadsheet mode: =VLOOKUP(a1;x1:y99;2) MATCH(a1;x1:x99)
Spreadsheet mode: =VLOOKUP(a1;x1:y99;2;1) MATCH(a1;x1:x99;1)
Database mode: =VLOOKUP(a1;x1:y99;2;0) MATCH(a1;x1:x99;0)

1 Like

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