Forum Discussion

SimenAlm's avatar
SimenAlm
Copper Contributor
Mar 06, 2020

Xlookup/Find row issue

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? 

excel
Formulas and Functions
office 365

7 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Mar 06, 2020

    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.

    • SimenAlm's avatar
      SimenAlm
      Copper Contributor
      Mar 09, 2020

      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? 

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Mar 09, 2020

        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.

         

Resources