Vlookup Formatting #N/A

[Guest]

Hello. I am having a problem with vlookup anytime I am working with exports
from a particular online system. I've tried reformatting, copying and
pasting as values, trim, and clean, etc. Nothing has worked yet. Here is
what my to worksheets look like:
list
A1 A2 A3
EID Name SSN

all
A1 A2 A3 A4
Claim # SSN State Name (and other columns, these are the only ones I'm
concerned with)

I am trying to pull the state from "all" into "list." Here is my function:
=VLOOKUP(E5,'[All Open.xls]all'!$1:$65536,2,FALSE)

Thanks in advance for your help...I am so frustrated by this!

 

[Alan]

A possibility,
If you've imported data you may have the character ASCII1 60, in XL
CHAR(160), there which looks like a space but isn't ,a space is ASCII 32 or
in XL CHAR(32).
Try highlighting the data, Edit > Replace > hold down the right Alt key and
type 0160 on the keypad (nothing will appear in the dialogue box) > Replace
With > Nothing > Replace All. Now try the VLOOKUP,
Regards,

 

[Guest]

No luck...is my lookup function correct? My lookup functions work on other
workbooks.

 

[Paul Lautman]

Well, first of all it looks like the value that you wish to look up is in
cell A3 not in E5.

Secondly, the lookup table for VLOOKUP must be the first column of the
table, so I would try:
= VLOOKUP(A3,'[All Open.xls]all'!$B:$IV,2,FALSE)

 

[Alan]

Not sure, send me the file (or a bit of it) if you want to and exactly what
you want it to do on
(e-mail address removed) (remove nospam)
The address I use on here is a spam trap,
Regards

No luck...is my lookup function correct? My lookup functions work on other
workbooks.

Hello. I am having a problem with vlookup anytime I am working with exports
from a particular online system. I've tried reformatting, copying and
pasting as values, trim, and clean, etc. Nothing has worked yet. Here is
what my to worksheets look like:
list
A1 A2 A3
EID Name SSN

all
A1 A2 A3 A4
Claim # SSN State Name (and other columns, these are the only ones I'm
concerned with)

I am trying to pull the state from "all" into "list." Here is my function:
=VLOOKUP(E5,'[All Open.xls]all'!$1:$65536,2,FALSE)

Thanks in advance for your help...I am so frustrated by this!

 

[Guest]

Paul, you're correct, in the example I gave, I would be looking for A3. What
exactly is defined as the "lookup table." Is that the column where the data
in A3 resides in "all?" I don't understand why in the new formula, the table
array starts in column B. Is it because the lookup table is in Column A?

Thanks for the help...I haven't had any of these problems with other
lookups...that's why I'm a bit confused.

Well, first of all it looks like the value that you wish to look up is in
cell A3 not in E5.

Secondly, the lookup table for VLOOKUP must be the first column of the
table, so I would try:
= VLOOKUP(A3,'[All Open.xls]all'!$B:$IV,2,FALSE)

Hello. I am having a problem with vlookup anytime I am working with exports
from a particular online system. I've tried reformatting, copying and
pasting as values, trim, and clean, etc. Nothing has worked yet. Here is
what my to worksheets look like:
list
A1 A2 A3
EID Name SSN

all
A1 A2 A3 A4
Claim # SSN State Name (and other columns, these are the only ones I'm
concerned with)

I am trying to pull the state from "all" into "list." Here is my function:
=VLOOKUP(E5,'[All Open.xls]all'!$1:$65536,2,FALSE)

Thanks in advance for your help...I am so frustrated by this!

 

[Paul Lautman]

Well, first of all it looks like the value that you wish to look up is in
cell A3 not in E5.

Secondly, the lookup table for VLOOKUP must be the first column of the
table, so I would try:
= VLOOKUP(A3,'[All Open.xls]all'!$B:$IV,2,FALSE)

 

[Paul Lautman]

I can do no better here than quote from the help page for VLOOKUP:


Syntax

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value is the value to be found in the first column of the array.
Lookup_value can be a value, a reference, or a text string.


HTH

Paul, you're correct, in the example I gave, I would be looking for A3. What
exactly is defined as the "lookup table." Is that the column where the data
in A3 resides in "all?" I don't understand why in the new formula, the table
array starts in column B. Is it because the lookup table is in Column A?

Thanks for the help...I haven't had any of these problems with other
lookups...that's why I'm a bit confused.

Well, first of all it looks like the value that you wish to look up is in
cell A3 not in E5.

Secondly, the lookup table for VLOOKUP must be the first column of the
table, so I would try:
= VLOOKUP(A3,'[All Open.xls]all'!$B:$IV,2,FALSE)

Hello. I am having a problem with vlookup anytime I am working with exports
from a particular online system. I've tried reformatting, copying and
pasting as values, trim, and clean, etc. Nothing has worked yet. Here is
what my to worksheets look like:
list
A1 A2 A3
EID Name SSN

all
A1 A2 A3 A4
Claim # SSN State Name (and other columns, these are the only ones I'm
concerned with)

I am trying to pull the state from "all" into "list." Here is my function:
=VLOOKUP(E5,'[All Open.xls]all'!$1:$65536,2,FALSE)

Thanks in advance for your help...I am so frustrated by this!