SOLVED

vlookup #N/A issues - not a 'newbie'

Copper Contributor

I am struggling with a persisitant #N/A message.

Formula is this: =VLOOKUP(D6,'[Clients.xlsb]Jobs-Orders'!$A$3:$Y$10000,7,FALSE)

I have  checked the following:

D6 refers to the correct cell for the lookup_value, and is formatted as number, in this case 333.

The table_array is in another workbook, called Clients, and the sheet is Jobs-Orders. Both are open as I struggle with this.

The table_array is from $A$3 to $Y$390, but I have made it to $Y$10000 to allow for data growth (Yes, I know that it could have been a Table, but this goes way back in my learning curve).

The matching entry for the lookup_value is in Col.A of the 'source' workbook, hence in the leftmost column, and it too is formatted as Number. And 333 is definitely there.

The value I want returned is in Col.G, IE #7 for col_index_num, so seems correct.

What is wrong?!

I've used vlookup in plenty of other applications, and they have generally been successful.

14 Replies
I have tried again with the following formula, copied and pasted from another sheet in the same workbook, where the vlookup is working OK, and bringing up the exact same required data. Grrrr!!!!
=VLOOKUP(B1,'\\DOMINIC-PC\Documents\user-files\Documents\SUNNVENT\Clients\[Clients.xlsb]Jobs-Orders'!$A$3:$Y$10000,7,FALSE)
Copy the data to another workbook
Or use XlookUp

@Dominic_Joslin VLOOKUP returning an #N/A error means that it did NOT find a match. Are you sure that the numbers 333 are the same in both lists? What do you get when you enter a formula like 

 

=D6='[Clients.xlsb]Jobs-Orders'!$A$500

 

where A500 in the Clients file also contains the 333 you are trying to match. Change that reference to the one that really contains 333. Do your get TRUE or FALSE?

 

If FALSE, then both "numbers" are not the same. One might have been entered(imported) as text. Reformatting the cell/column to Number or General will not automatically transform a text from, for instance " 333 " (note the leading/trailing spaces) to a number 333.

Probably her use Vlookup (NumberValue(A2), Table_array, Col Index ,0)
Assuming the value in the table is a number

@Riny_van_Eekelen Thank you for replying.

I have just checked the 333 numbers, and in both places lookup_value, and table_array, they seem to be 'clean', IE no empty spaces before or after, and both formatted as numbers.

 

I tried to paste your suggested test formula, and twice, IE in two different cells it returns False. I tried it with both A500 and A333.

 

Curiously, when I try my formula in a cell on another Sheet in the Workbook, it works fine. ?? Could there be something up with the new sheet?

Do you mean copy the data in the sheet that I am trying to extract the data from, in this case the Clients workbook? Or the 'destination' workbook?
I have tried XLOOKUP, but didn't follow it all the way through, as the lookup_value is in the left most column of the sheet, so VLOOKUP seemed appropriate. I'll try it again, if nothing else sorts it out.
Hi Donald. I have just tried the formula with 0 instead of False as the [Range_Lookup], but it hasn't fixed it IE still #N/A.
best response confirmed by Dominic_Joslin (Copper Contributor)
Solution

@Dominic_Joslin The A500 in my formula was just an example. Find the actual cell reference of the value that should match what is in cell D6 (the lookup_value in the VLOOKUP function). Let's say that actual reference is A235, then somewhere else type  =D6=A235. If the result is FALSE, you know for sure that the two values are NOT the same.

 

By the way, I saw you post on Myonlinetraininghub where you asked the same question and where you received more or less the same answer.

Hi Riny. I just tried what you suggested, and the result was True. What I was doing before was entering the value of col/row A235 (for eg.), being 333 (IE: A333), rather than the proper cell reference. So your test indicates that the values are the same.
However, good news: the problem formula is now working correctly. I don't know exactly what action has fixed it, but I'm going to credit it to your input. So, thank you.
Hi
Column F contains a Unique number.
Output in Column G should indicate the TAZ linked to that Unique number.
My table array is columns I to J in the same worksheet.
Column I contains TAZ numbers while column J contains Unique numbers.
My formula in Column G (desired output) is: =VLOOKUP(F2;I:J;1;FALSE)
But I get N/A
Format of all cells in the formula is set to numerical. I've tried text as well.

@Mienke It might be because VLOOKUP only finds values to the right of the lookup reference column. 
If I understand you correctly, you have set up Col.I with the TAZ #'s (that are the values you want to return), and the unique #'s (that are your references) in Col.J, which is to the right of Col.I.

So, can you swap those columns around?

Alternatively, you could use XLOOKUP, which will search L or R.

Hopefully this helps, but I am not an expert, so no guarantees.

Index and match together also works

@Dominic_Joslin  wrote:  ``I don't know exactly what action has fixed it``

 

The original problem was probably because the value in D6 and/or A235 is calculated, and one or both are infinitesimally different from 333.

 

They might appear to be 333 because Excel formats only up to 15 significant digits.

 

Copy-and-pasting the displayed value corrected the problem because that eliminates any invisible infinitesimal difference.

 

VLOOKUP(...,FALSE) requires exact binary equality.

 

=D6=A235 is not sufficient to test for exact binary equality because the "=" operator rounds the operands to 15 significant digits internally just for the purpose of comparison.

 

One correct way to test for binary equality is =D6-A235=0.

 

(Yes, mathematically they should be same.  Sigh.)

 

You can see the infinitesimal differences, if any, with the following formulas, formatted as Scientific:

=SUM(D6,-(D6&""))

=SUM(A235,-(A235&""))

 

0.00E+00 is exactly zero, which indicates no infinitesimal difference between the binary representation of the displayed value (D6&"") and the actual value (D6).

 

FYI, we must use SUM instead of =D6-(D6&"") because for formulas of that form, sometimes Excel replaces the infinitesimal difference with exactly zero arbitrarily because it decides that the two operands are "close enough".

 

As an example, enter 333 into A1, and enter =333+2^-44 into A2.

 

Note that A2 displays 333, even when formatted to display 12 or more decimal places (15 significant digits).

 

=VLOOKUP(A1,A2,1,FALSE) returns #N/A, which is correct.

 

=A1=A2 returns TRUE, and =A1-A2 displays 0.00E+00 when formatted as Scientific.

 

But =A1-A2=0 returns FALSE, which is correct.

 

=SUM(A1,-(A1&"")) displays 0.00E+00, but =SUM(A2,-(A2&"")) displays 5.68E-14 when formatted as Scientific.

 

Hope that helps if and when your encounter this problem in the future.  It is more common than people think.

 

Thank you for the explanation. Shows how deep knowledge can go. Scary!
1 best response

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

@Dominic_Joslin The A500 in my formula was just an example. Find the actual cell reference of the value that should match what is in cell D6 (the lookup_value in the VLOOKUP function). Let's say that actual reference is A235, then somewhere else type  =D6=A235. If the result is FALSE, you know for sure that the two values are NOT the same.

 

By the way, I saw you post on Myonlinetraininghub where you asked the same question and where you received more or less the same answer.

View solution in original post