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.

 

[Aladin Akyurek]

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)?

2E+11

is a display format. Excel sees a number and that number is: 200000170101.

What does the following do?

Control+shift+enter:

=INDEX('[chntrp09 dustyn working.xlsx]chntrp09.csv'!$F:$F,
  MATCH(D2&"",'[chntrp09 dustyn working.xlsx]chntrp09.csv'!$E:$E,0))

 

[dylbertiii]

Re: 2E+11 Aha, i see.

It gets me #N/A's for all entries (no successful entries for some like i had with VLOOKUP).

It just occurred to me that i might have a couple versions of chntrp09 dustyn working saved in different places (I know, not very organized, but i've been trying different things and been quick-saving to desk top with one version, for example), and that [chntrp09 dustyn working.xlsx]chntrp09.csv] doesn't specify a file directory. Could it be that it doesn't know which chntrp09 dustyn working.xlsx to reference?

 

[Aladin Akyurek]

Re: 2E+11 Aha, i see.

It gets me #N/A's for all entries (no successful entries for some like i had with VLOOKUP).

It just occurred to me that i might have a couple versions of chntrp09 dustyn working saved in different places (I know, not very organized, but i've been trying different things and been quick-saving to desk top with one version, for example), and that [chntrp09 dustyn working.xlsx]chntrp09.csv] doesn't specify a file directory. Could it be that it doesn't know which chntrp09 dustyn working.xlsx to reference?

Yes...

 

[dylbertiii]

No cigar. I deleted all the similar files i could find, but it didn't work. So...

I deleted all the files that had anything to do with this research, gutted my trash bin, downloaded them fresh from the source's site, thinned them down again, created the formulas for the id's. Saved both sheets as xlsx (if tried to save them in the original csv format, it warned me that there might be errors-???). VLOOKUP function worked this time for all desired cells.

Thanks for the help Aladin. It's been very much appreciated!

 

[Aladin Akyurek]

No cigar. I deleted all the similar files i could find, but it didn't work. So...

I deleted all the files that had anything to do with this research, gutted my trash bin, downloaded them fresh from the source's site, thinned them down again, created the formulas for the id's. Saved both sheets as xlsx (if tried to save them in the original csv format, it warned me that there might be errors-???). VLOOKUP function worked this time for all desired cells.

Thanks for the help Aladin. It's been very much appreciated!

You are welcome.

 

[sakthikumar]

check both the data if there is any duplicate value in it..........and make sure in table array you should use $....

 

[Aladin Akyurek]

check both the data if there is any duplicate value in it..........and make sure in table array you should use $....

Duplicate values do not cause #N/A at all.