Can't get XLOOKUP to work

[vahapesa]

Hi

I'm new to forum, and I have Finnish version of Excel, sorry for bad language/incorrect term.

In brief, what I'am doing:
I have hundreds of lines of CAN-BUS messages. They are in hexadesimal format, and four characters in middle defines Parameter Group Number)pgn). In example, sheet 1, column A I have hexadesimal adresses. In column B, I used POIMI.TEKSTI, I guess it's LEFT(?) to extract those four characters. In column C, Hex is transformed to decimal with HEKSADES (HEX2DES). In second sheet, (sorry, couldn't cut it just to make sure all numbers appear) I have J1939 Can-bus standard, where are listed which pgn:s belong to standard. It was in pdf format, so I had to do another LEFT to get five digits after pgnXXXXX.

First thing I have to do, is to find out if pgn in first sheet is in J1939 or not. I tried XLOOKUP (XHAKU).

in cell D3 I have =XLOOKUP(C3;'J1939'!B4:B199;'J1939'!C4:C199;"No")

I have pgn number in sheet1 Column C, so i'm trying if C3 can be found in J1939, pgn is in column B in 1939. I also added column C to 1939, filled with "Yes". So, if value of C3 is in J1939 column B, it would return value from J1939 column C, which is always "Yes", if not, it should return "No"

In all cases function returns "No", even though numbers can be found in J1939 column B.

What I dont understand, am I missing something?

All numbers in example sheet 1 are in J1939, looked them manually.

example.xlsx

[AliGW]

Welcome to the forum.

In B4 copied down:

=--MID(A4;4;5)

The -- will convert text to a number. Then the XLOOKUP will work.

[vahapesa]

Thank you I had never heard about that "--" before.

[Fluff13]

even though numbers can be found in J1939 column B.

There are no numbers in col B, the MID function returns text.
Try

Please Login or Register  to view this content.

[AliGW]

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.