Vlookup error #N/A

[dylbertiii]

I'm receiving #N/A for most (about 95%) of attempted VLOOKUP entries. Does it matter that my reference array is in a xlsx and where i'm wanting it to land is in a csv?

Also, might be helpful to know that the value on the csv that i'm looking up is a result of a function that reads 2E+11. If i format cell to read as a number, i get my desired entry, 2000001701. But then, in the function bar, it displays only the number, no longer the function it took to get there.

I'm using Excell 11 for Mac.

 

[dylbertiii]

I made it to where i'm now working with two xlsx spreadsheets. Also, i stripped the function by copying and pasting special the value so that i have 2000001701 (for example) so that i have the same read in the cell and function boxes for the corresponding columns. Still getting a lot of, but not all, #N/A. Sample:

[TABLE="width: 193"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]200000170101[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]200000170102[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]200000170103[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]200000170104[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]200000170105[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]200000170201[/TD]
[TD] HO
[/TD]
[/TR]
[TR]
[TD="align: right"]200000170202[/TD]
[TD] HO
[/TD]
[/TR]
[TR]
[TD="align: right"]200000170203[/TD]
[TD] OO
[/TD]
[/TR]
[TR]
[TD="align: right"]200000170204[/TD]
[TD] OO
[/TD]
[/TR]
[TR]
[TD="align: right"]200000170205[/TD]
[TD] OH
[/TD]
[/TR]
[TR]
[TD="align: right"]200000170206[/TD]
[TD] OH
[/TD]
[/TR]
[TR]
[TD="align: right"]200000170207[/TD]
[TD] HO
[/TD]
[/TR]
[TR]
[TD="align: right"]200000170208[/TD]
[TD] OH
[/TD]
[/TR]
[TR]
[TD="align: right"]200002310101[/TD]
[TD="align: center"]#N/A
[/TD]
[/TR]
[TR]
[TD="align: right"]200002310102[/TD]
[TD="align: center"]#N/A
[/TD]
[/TR]
[TR]
[TD="align: right"]200002310103[/TD]
[TD="align: center"]#N/A
[/TD]
[/TR]
[TR]
[TD="align: right"]200002310104[/TD]
[TD="align: center"]#N/A
[/TD]
[/TR]
[TR]
[TD="align: right"]200002310105[/TD]
[TD="align: center"]#N/A
[/TD]
[/TR]
[TR]
[TD="align: right"]200005210101[/TD]
[TD="align: center"]#N/A
[/TD]
[/TR]
[TR]
[TD="align: right"]200005210102[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]200005210103[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]200005210104[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]200005210201[/TD]
[TD] HO
[/TD]
[/TR]
[TR]
[TD="align: right"]200005210202[/TD]
[TD] OH
[/TD]
[/TR]
[TR]
[TD="align: right"]200012830101[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]200012830102[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]
...

 

[Aladin Akyurek]

You could check manually whether 200000170101 for example really exist in the area where you are seeking the value.

 

[dylbertiii]

It does exist (first row of data in either document). And when i copy the contents of the cell in one and paste it in a Cmmd+F on the other document, it finds it just find.

Here's an example of my Vlookup formula: =VLOOKUP(D2,'[chntrp09 dustyn working.xlsx]chntrp09.csv'!$E:$F,2,FALSE)

 

[Aladin Akyurek]

It does exist (first row of data in either document). And when i copy the contents of the cell in one and paste it in a Cmmd+F on the other document, it finds it just find.

Here's an example of my Vlookup formula: =VLOOKUP(D2,'[chntrp09 dustyn working.xlsx]chntrp09.csv'!$E:$F,2,FALSE)

Does one of the following return the desired result?

=VLOOKUP(D2&"",'[chntrp09 dustyn working.xlsx]chntrp09.csv'!$E:$F,2,FALSE)

=VLOOKUP(D2+0,'[chntrp09 dustyn working.xlsx]chntrp09.csv'!$E:$F,2,FALSE)

 

[dylbertiii]

If i copy and paste either of those into the function field, it reacts as if i'm typing in a value.

 

[Aladin Akyurek]

If i copy and paste either of those into the function field, it reacts as if i'm typing in a value.

I don't understand. I'm asking a simple edit regarding the formula you already have.

 

[dylbertiii]

Right. I tried copying and pasting them from here. Wasn't working. Closed excel, reopened it, tried both of the suggested formulas, neither worked. Results are exactly the same (same #N/A and successful results as the original formula.

 

[Aladin Akyurek]

Right. I tried copying and pasting them from here. Wasn't working. Closed excel, reopened it, tried both of the suggested formulas, neither worked. Results are exactly the same (same #N/A and successful results as the original formula.

You said:

1) The value of D2, that is, 200000170101, exists in column E of [chntrp09 dustyn working.xlsx]chntrp09.csv.

2) The foregoing values appear to have the same format (text or number) for D2&"" and D2+0 did not change the #N/A result.

I'd suggest double checking..., short of seeing the files.

 

[dylbertiii]

Thanks for your help. When i'm entering in the VLOOKUP function in the formula builder mini window, it shows a preview for the lookup_value after i click the cell. It reads 2E+11, not 200000170101. Does that mean anything?

Double checked. It finds it just fine in a Command Find query. Double checked that the cells are numbers in both cases. I also erased all other columns but the two i'm working with in the reference sheet.

Can't post the files here; are there alternatives so that you might be able to see them (if you're willing)?