Problem with lookup

English
#1

Hi everybody,
I have two problems.

In the sheet circo I use lookup at each row of the column E to look for the number of the column A which is in in the sheet legi somewhere in the column D and which corresponds at this row to a number of the column F (of this page). There are way too much number 3 in the column E. The column F doesn’t have so much number 3.
For instance for the first row of the sheet circo, there is the number 59416 in the column A. In the sheet legi 59416 is in the column D and shows 15 in the column F. But lookup shows 3.
I don’t understand this. Please could I get some help ?

When there is a text in the column A, lookup “dies” and I have no result.
I would be glad to learn how to solve this problem.

The file is here:

#2

LOOKUP does not do what most users expect it to do because spreadsheets were not designed as databases, even if most people try to use spreadsheets as databases. The LOOKUP function remains the same since Visicalc of 1979.

#3

This is a matter of reading documentation carefully, and checking the stated requirements.

Specifically, this clause in the documentation is relevant:

the search vector for the LOOKUP must be sorted ascending, otherwise the search will not return any usable results

You may think that your data meets the requirement. But it does not. legi.D2:D36467 has a mix of text and numbers; it starts with texts like ZA101; then it continues with numbers like 11368; and then go texts like 56199 (coming from formulas concatenating characters). Lines 474 and 7636 break the D[this] >= D[above] rule.

And indeed, when you fix the data in the legi, you need to make sure that the data in circo.A:A has the same type as the fixed data in legi.D:D. E.g., if you would decide to make all data in legi.D:D texts, you will need to make the same change to circo.A:A.

1 Like
#4

Thank you very much mikekaganski.

The column A from sheet circo and the column D from sheet legi have both numbers and codes (mix of numbers and letters). I’m not responsible :slight_smile:

Finally I used match and index instead of lookup.
Thank you again.

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