Xlookup/Find row issue

[SimenAlm]

Hello,

I've got a column of data(J) that I need to compare with another column (C). If the data matches, I then want the data of a third column (K) to be saved in the cell. I've used two different formulas to try and get this right, but I can see that it still does not work.

I've used "=XLOOKUP(C5,$J$5:$J$12959,$K$5:$K$12959,$M$5)" and "=FIND.ROW(C5,$J$5:$K$12960,2,FALSE)

However, I can see that the output is wrong in the majority of the results. I'm checking this manually, and even though I can see that a match between column C and J exists, it says #I/T - basically that it does not exist.

I dont know why.. Is it because of the sorting? I've tried to sort the two columns the same way, but even though I format them as the same type of typing I get different filter options.... Not sure if i'm heading in the right direction here though.

Any help? 

[#2]

@SimenAlm Both your formulae are in principle correct. The #I/T error (#N/A in English) suggests that the value in C5 that you are trying to match via XLOOKUP or FINN.RAD (VLOOKUP in English) does not exist in column J. Note that there has to be an exact match, otherwise you will get #N/A error.

Are you looking up a number or a text? A number is not the same as a text looking like a number. A text with a space in front or behind is not the same as a text without the space.

[#3]

Im looking up mostly numbers, but some of the numbers have a letter to start with. Example D53421 (the numbers with the letters got it in both the columns!)

Not sure why this happens though. I did check some examples and they did not have any space in front or back of the number.

Does the formatting mean anything? 

[#4]

@SimenAlm Difficult to say. Can you upload an example of the values (numbers and/or texts) you are trying to match?

Otherwise, if you have two values for which you believe that they are the same, type a formula similar to this:

=A1=B1

If these two cells are indeed the same, it should return TRUE (SANN). If it does not, they are not exactly the same and your lookup function will not make the match.

[#5]

@Riny_van_Eekelen I did this test to make sure that it was indeed "SANN", and it was.

I checked cell J11961 up against C12450.

I've attached the document i'm working on. I deleted some columns though, but they don't matter in this case.

[#6]

@SimenAlm As suspected, your "Numbers" in column C are NOT real numbers, whereas the ones you were trying to match them with in column I are. Did a "Data / Text-columns" on C as "General". Now it works and you only have a handful of N/A's left.

[#7]

Thanks!

I tried it myself and I can see that it now works perfect.

I thought it should be enough to "format" as numbers, but it obviously wasn't..

[#8]

@SimenAlm Glad you figured it out! Now, there is a trick to make Excel match a number with a text that looks like a number. Putting a double hyphen ("--") in front of the cell reference that contains the number that could be text, will make it a number. For instance:

=A1=--B1

... this will return TRUE when A1 contains the number e.g. 1766 and B2 contains a text "1766". This works if the text only contains numbers. The text "A1766" will become #VALUE!.

You could consider working this into you formulae if you suspect that numbers might not be what they look like, but personally I favour to be sure and focus on having clean data to begin with and work with that. Garbage In = Garbage Out.