SOLVED

#N/A in formula from data linked between workbooks

Copper Contributor

 Hello,

I have few workbooks which links data from one to another. For example, I have a sheet with list of trucks and owners (company name 1st column and truck plate 2nd column) linked from another WB. In another WB I have the list of trucks from intake report and I want to identify the owner from the list of trucks by bringing the company name.

My formula is looking like that = VLOOKUP (O25; 'LISTA AUTO'! $N$3:$O$2693 ; 1 ; TRUE ) where O25 is the cell with truck number, range to lookup for data is N3:O2693, 1 is the column number to bring and true is the match. If I use false as a match brings me #N/A on all column.

Results is that, my formula doesn't bring correct data meaning wrong owner or showing #N/A.

Evaluation of the formula by step in for #N/A, says that the truck no from O25 is not recognized or found in my list.

The list of trucks are the same used in the intake report, I just used it the same for my simulation.

I want to understand what is wrong with my formula and how to correct it to work out.

I use MS365 and W10 if this is useful.

7 Replies
best response confirmed by CarmenV (Copper Contributor)
Solution

@CarmenV From what you describe, I believe you need to look at the 2nd column and use FALSE.

Hi @Riny_van_Eekelen,
Thank you for your approach. As I said in my first post, if I use FALSE result is #N/A all column. So, is not workable this way. I believe the origin of my error stays in linked between sheets but cant figure out why and how to fix it.
After many tries by reducing/changing the range area and revers the columns, my formula at the end is working properly as you said with FALSE and 2nd column.
So formula now is =VLOOKUP(O25;'LISTA AUTO'!$B$3:$C$2693;2;FALSE)
Thanks Riny_van_Eekelen - You gave me reason to continue and find the way :)

@CarmenV Wait a minute. Trying to visualise your schedule. You say to have the the name in the first column and the plate in the second. And you look up the plate to find the name. Then you need to move the plate column towards the left of the name column and then look at the 2nd column with FALSE.

 

EDIT: Alternatively, see if your Excel supports XLOOKUP.

 

If this all seems nonsense, please upload your schedule to avoid any further confusion.

@Riny_van_Eekelen I did so and is working. I understood later on your 1st comment. VL formula is ALWAYS looking from left to right :) Thank you again for your support.
Carmen,
For the vlookup function, the lookup value in your lookup data, should be in the first colmn. In your situation the lookup value is in column 2.
If you change the list of trucks to truck plate in column 1 and company name in 2, you can return colmn 2 as name. You should use flase to get only exact maches.
If you cannot change the lookup table you could try xlookup or, in case you have an older version of Excel, you can used Index Match function.
Andre_nl , I did and is working. My comments above are in respect of this answer. Thank you too, to be involved in my question.
1 best response

Accepted Solutions
best response confirmed by CarmenV (Copper Contributor)
Solution

@CarmenV From what you describe, I believe you need to look at the 2nd column and use FALSE.

View solution in original post