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

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.

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))``````

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)`.

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.

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)

Why not TRIM() ?

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.