Ask Your Question
0

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

asked 2020-10-20 12:40:37 +0200

Moshpirit gravatar image

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
2

answered 2020-10-20 13:10:35 +0200

JohnSUN gravatar image

updated 2020-10-20 13:11:25 +0200

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))
edit flag offensive delete link more

Comments

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

Moshpirit gravatar imageMoshpirit ( 2020-10-20 13:39:58 +0200 )edit
2

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.

JohnSUN gravatar imageJohnSUN ( 2020-10-20 14:05:56 +0200 )edit

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)

Moshpirit gravatar imageMoshpirit ( 2020-10-20 15:21:05 +0200 )edit

Why not TRIM() ?

JohnSUN gravatar imageJohnSUN ( 2020-10-20 16:48:17 +0200 )edit

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.

Moshpirit gravatar imageMoshpirit ( 2020-10-20 16:55:03 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-10-20 12:40:37 +0200

Seen: 50 times

Last updated: Oct 20 '20