#n/a error on vlookup

[akswartz85]

Hello,

I am trying to do a vlookup across two sheets. The formula I have entered is as follows:

=VLOOKUP(AJ3,'Unis & Selectivity'!$A$2:$D$4553,4,FALSE)

AJ3- lookup value
'Unis & Selectivity'!$A$2:$d$4553 - table array - located in second sheet
4 - column index number
False- range lookup

However, it gives me the dreaded #n/a error, even though I've doubled checked that the expected value does exist in the second spreadsheet. I've redone it several times but to no avail. I am using Excel 10, and working off a desktop-based file (not on the server).

Any ideas? I can't tell what I am doing wrong here.

Thanks in advance!

 

[Aladin Akyurek]

Does any of these...

=VLOOKUP(TRIM(AJ3),'Unis & Selectivity'!$A$2:$D$4553,4,FALSE)

=VLOOKUP(AJ3+0,'Unis & Selectivity'!$A$2:$D$4553,4,FALSE)

=VLOOKUP(AJ3&"",'Unis & Selectivity'!$A$2:$D$4553,4,FALSE)

succeed?

 

[akswartz85]

Does any of these...

=VLOOKUP(TRIM(AJ3),'Unis & Selectivity'!$A$2:$D$4553,4,FALSE)

=VLOOKUP(AJ3+0,'Unis & Selectivity'!$A$2:$D$4553,4,FALSE)

=VLOOKUP(AJ3&"",'Unis & Selectivity'!$A$2:$D$4553,4,FALSE)

succeed?

No -- they don't. I just keep getting the #n/a error. Any other ideas? Might it have something to do with the format - .xlsx? It has more than 65k rows (about 82K), so I can't resave in .xls.

 

[Marcelo Branco]

Another try

=VLOOKUP(TRIM(AJ3),TRIM('Unis & Selectivity'!$A$2:$D$4553),4,FALSE)
confirmed with Ctrl+Shift+Enter

M.

 

[texasalynn]

Hello,

I am trying to do a vlookup across two sheets. The formula I have entered is as follows:

=VLOOKUP(AJ3,'Unis & Selectivity'!$A$2:$D$4553,4,FALSE)

AJ3- lookup value
'Unis & Selectivity'!$A$2:$d$4553 - table array - located in second sheet
4 - column index number
False- range lookup

However, it gives me the dreaded #n/a error, even though I've doubled checked that the expected value does exist in the second spreadsheet. I've redone it several times but to no avail. I am using Excel 10, and working off a desktop-based file (not on the server).

Any ideas? I can't tell what I am doing wrong here.

Thanks in advance!

one way I check is to change your table array to be the row that matches your lookup for a single row, then use the dialog box to see if the values look different

 

[akswartz85]

Another try

=VLOOKUP(TRIM(AJ3),TRIM('Unis & Selectivity'!$A$2:$D$4553),4,FALSE)
confirmed with Ctrl+Shift+Enter

M.

That isn't working either. It gives me a "FALSE" return.

 

[Aladin Akyurek]

No -- they don't. I just keep getting the #n/a error. Any other ideas?

What does...

=COUNTIF('Unis & Selectivity'!$A$2:$A$4553,"*"&AJ3&"*")

yield?

Might it have something to do with the format - .xlsx? It has more than 65k rows (about 82K), so I can't resave in .xls.

No, these have nothing to do with it...

 

[akswartz85]

What does...

=COUNTIF('Unis & Selectivity'!$A$2:$A$4553,"*"&AJ3&"*")

yield?

That yields a "0".... even for values we expect to receive an result.

 

[Marcelo Branco]

That isn't working either. It gives me a "FALSE" return.

humm...strange a VLOOKUP returning a FALSE value...

 

[Aladin Akyurek]

That yields a "0".... even for values we expect to receive an result.

What do we get with:

=LEN(AJ3)

=LEN(The Cell In 'Unis & Selectivity'!$A$2:$A$4553 which is supposed to be equal to AJ3)