IDK why FIND() gives me this value when comparing these two tables on LO Calc

[Moshpirit]

I have two tables:

• columns A (names) and B (empty)
• columns C (names) and D (values)

Columns A and B are sorted in a sheet in a way, C and D are sorted differently in another sheet. I want to get the values of column D in B respecting the way is sorted in the first sheet. In order to make it simple here, we’ll say they all are in the same sheet but with a different sorting method.

Here’s an little example where I removed most of the cells:

| Column A   | Column B | Column C  | Column D |
|------------|----------|-----------|----------|
| SI06SE020  |          | SI06JA038 | 98,66    |
| SI06JA023  |          | MI06JA109 | 96,8     |
| SI06JA038  |          | SI06JA033 | 94,98    |
| SI06JA039  |          | SI06JA107 | 94,91    |
| SI06JA040  |          | SI06JA124 | 94,81    |
| SI06JA124  |          | SI06JA039 | 91,18    |
| SI06SE139  |          | SI06SE140 | 91,28    |
| MI06HU065  |          | SI06SE020 | 86,82    |

As you can see, A2 (SI06SE020) is the same value as the latest of column C.

I’m trying to get D values comparing A with C with Find(), so B2 formula would be =FIND(A2;$C$2:$C$57;$D$2:$D$57). Instead of getting 86,82, I’m getting 94,81 (which belongs to SI06JA039) but have no idea of why.

[JohnSUN]

It was a bad idea to use the FIND() function for this task, it is meant to find a substring in text, not a cell in a range. In your case, it is better to use the VLOOKUP() function specially designed for such tasks, or a combination of the MATCH() and INDEX() functions.

=VLOOKUP(A2;$C$2:$D$57;2;0)
=INDEX($D$2:$D$57;MATCH(A2;$C$2:$C$57;0))

[Moshpirit]

Thanks! I tried VLOOKUP before but I got #N/A instead of the result. Even though there’s 0 in the last variable of =VLOOKUP(A2;$C$2:$D$57;2;0).

[JohnSUN]

This can happen if in one of the columns the value ends with a space - you do not see it, but it is there. And for VLOOKUP(), the values ​​"SI06SE020" and "SI06SE020 " are different. Another mistake that can lead to #N/A is relative rather than absolute addresses of ranges in the formula ($ sign). In this case, the formula works well for one cell, but after stretching to the entire column it starts giving errors.

[Moshpirit]

That was it!! thanks a lot! I think LO should have (if it doesn’t) some tool to remove latest spaces.

(I made a different column with =SUBSTITUTE(A2;" ";"") for this purpose)

[JohnSUN]

Why not TRIM() ?

[Moshpirit]

Because I had no idea that such tool existed. I should search for some cheatsheet for LO Calc and keep it around when I’m using it.