Need to be done in 2 hours!!!! VLOOKUP returning #REF for unique I
 

My data has 3 columns that id a value. I have concatenated them to create a
unique ID. In the lookup.xls I created a map for each unique ID to the
desired output text. The uniqueID is in col 4, row2; the range that contains
my mapping (unique ID in left column, sorted ascending, and text to
assign/retrieve in next column) is in the lookup.xls as a range named
Unique_MPC_Unit. I cannot get this to work. Is it because the all my
columns contain text of variable widths?

=VLOOKUP(RC[-1],Lookup.xls!Unique_MPC_Unit,5,FALSE)
--
Kay Mejia

Why are you using R1C1 style not A1

=VLOOKUP(D2,Lookup.xls!Unique_MPC_Unit,5,FALSE)

I cannot see variable widths being the issue, but perhaps you could post
some data.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kay" wrote in message
...
My data has 3 columns that id a value. I have concatenated them to create
a
unique ID. In the lookup.xls I created a map for each unique ID to the
desired output text. The uniqueID is in col 4, row2; the range that
contains
my mapping (unique ID in left column, sorted ascending, and text to
assign/retrieve in next column) is in the lookup.xls as a range named
Unique_MPC_Unit. I cannot get this to work. Is it because the all my
columns contain text of variable widths?

=VLOOKUP(RC[-1],Lookup.xls!Unique_MPC_Unit,5,FALSE)
--
Kay Mejia

if your lookup table only has two columns shouldnt your 5 be a 2?
as long as your text is actually typed in the same column you will be ok
I dont ussually use rc type references but that doesnt look right for the
4th column and second row
--
paul
remove nospam for email addy!



"Kay" wrote:

My data has 3 columns that id a value. I have concatenated them to create a
unique ID. In the lookup.xls I created a map for each unique ID to the
desired output text. The uniqueID is in col 4, row2; the range that contains
my mapping (unique ID in left column, sorted ascending, and text to
assign/retrieve in next column) is in the lookup.xls as a range named
Unique_MPC_Unit. I cannot get this to work. Is it because the all my
columns contain text of variable widths?

=VLOOKUP(RC[-1],Lookup.xls!Unique_MPC_Unit,5,FALSE)
--
Kay Mejia

Hi

How is the range Unique_MPC_Unit defined? To use it in VLOOKUP, the column D
must be leftmost. And when you have 3rd parameter for VLOOKUP set to 5, then
the return value must be in 5th column IN RANGE Unique_MPC_Unit - not in 5th
column on source sheet!


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )


"Kay" wrote in message
...
My data has 3 columns that id a value. I have concatenated them to create
a
unique ID. In the lookup.xls I created a map for each unique ID to the
desired output text. The uniqueID is in col 4, row2; the range that
contains
my mapping (unique ID in left column, sorted ascending, and text to
assign/retrieve in next column) is in the lookup.xls as a range named
Unique_MPC_Unit. I cannot get this to work. Is it because the all my
columns contain text of variable widths?

=VLOOKUP(RC[-1],Lookup.xls!Unique_MPC_Unit,5,FALSE)
--
Kay Mejia

I changed the references to cell style and now it looks like:
=VLOOKUP(D2,Lookup.xls!Unique_MPC_Unit,5,FALSE)

The data I am searching in is in the 4th column of the current sheet and I
want the results of lookup to be in the 5th column. Here is a sample of the
current sheet:
CO UNIT ACCOUNT Unique ID MPC Unit Affiliated Unit
1 AL001 2260-0001 1AL0012260-0001 #REF! (affiliated unit is where vlookup
results should be)

This is from the lookup.xls, wyhere unique ID and MPC Unit columns are the
range named Unique_MPC_Unit:

Lawson Acct Lawson Unit Unique ID MPC Unit
1839*0000 1839*0000 HD001

Thanks for your help!
--
Kay Mejia


"Bob Phillips" wrote:

Why are you using R1C1 style not A1

=VLOOKUP(D2,Lookup.xls!Unique_MPC_Unit,5,FALSE)

I cannot see variable widths being the issue, but perhaps you could post
some data.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kay" wrote in message
...
My data has 3 columns that id a value. I have concatenated them to create
a
unique ID. In the lookup.xls I created a map for each unique ID to the
desired output text. The uniqueID is in col 4, row2; the range that
contains
my mapping (unique ID in left column, sorted ascending, and text to
assign/retrieve in next column) is in the lookup.xls as a range named
Unique_MPC_Unit. I cannot get this to work. Is it because the all my
columns contain text of variable widths?

=VLOOKUP(RC[-1],Lookup.xls!Unique_MPC_Unit,5,FALSE)
--
Kay Mejia

Do I have this backward? I need to find the value located in the
lookup_value by searching in the table_array, returning the 2nd column from
the array to the column in the col_index_num location.
--
Kay Mejia


"Kay" wrote:

My data has 3 columns that id a value. I have concatenated them to create a
unique ID. In the lookup.xls I created a map for each unique ID to the
desired output text. The uniqueID is in col 4, row2; the range that contains
my mapping (unique ID in left column, sorted ascending, and text to
assign/retrieve in next column) is in the lookup.xls as a range named
Unique_MPC_Unit. I cannot get this to work. Is it because the all my
columns contain text of variable widths?

=VLOOKUP(RC[-1],Lookup.xls!Unique_MPC_Unit,5,FALSE)
--
Kay Mejia

Be sure your named lookup range includes BOTH the column with the values you
are looking up, AND the column with the values you are wishing to
return.........( I make this mistake all the time, especially when I'm in a
hurry)

Typical formula without rangenames on same sheet would look like

=VLOOKUP(A1,G1:H10,2,FALSE)

If you wanted to step over 5 columns,

=VLOOKUP(A1,G1:K10,5,FALSE)


Vaya con Dios,
Chuck, CABGx3


"Kay" wrote in message
...
Do I have this backward? I need to find the value located in the
lookup_value by searching in the table_array, returning the 2nd column
from
the array to the column in the col_index_num location.
--
Kay Mejia


"Kay" wrote:

My data has 3 columns that id a value. I have concatenated them to
create a
unique ID. In the lookup.xls I created a map for each unique ID to the
desired output text. The uniqueID is in col 4, row2; the range that
contains
my mapping (unique ID in left column, sorted ascending, and text to
assign/retrieve in next column) is in the lookup.xls as a range named
Unique_MPC_Unit. I cannot get this to work. Is it because the all my
columns contain text of variable widths?

=VLOOKUP(RC[-1],Lookup.xls!Unique_MPC_Unit,5,FALSE)
--
Kay Mejia

although it is indeed the fifth column in the sheet its only the 2nd column
in your lookup table,arvi has noted the same below
--
paul
remove nospam for email addy!



"Kay" wrote:

Do I have this backward? I need to find the value located in the
lookup_value by searching in the table_array, returning the 2nd column from
the array to the column in the col_index_num location.
--
Kay Mejia


"Kay" wrote:

My data has 3 columns that id a value. I have concatenated them to create a
unique ID. In the lookup.xls I created a map for each unique ID to the
desired output text. The uniqueID is in col 4, row2; the range that contains
my mapping (unique ID in left column, sorted ascending, and text to
assign/retrieve in next column) is in the lookup.xls as a range named
Unique_MPC_Unit. I cannot get this to work. Is it because the all my
columns contain text of variable widths?

=VLOOKUP(RC[-1],Lookup.xls!Unique_MPC_Unit,5,FALSE)
--
Kay Mejia

Your formula should look like this:-

=VLOOKUP(D2,Lookup.xls!Unique_MPC_Unit,2,FALSE)

What this will do is to take the value that is in D2 in your current
workbook, then go to your named range in the file Lookup.xls and look for
that value in the leftmost column of your range (This is column 1 of your
range, regardless of which column it is, eg A, B, C...). When it finds it
it will return the corresponding value from the column to the right of where
it finds the value (From your text I would say this is column 2, hence the 2
in the formula).

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"Kay" wrote in message
...
Do I have this backward? I need to find the value located in the
lookup_value by searching in the table_array, returning the 2nd column
from
the array to the column in the col_index_num location.
--
Kay Mejia


"Kay" wrote:

My data has 3 columns that id a value. I have concatenated them to
create a
unique ID. In the lookup.xls I created a map for each unique ID to the
desired output text. The uniqueID is in col 4, row2; the range that
contains
my mapping (unique ID in left column, sorted ascending, and text to
assign/retrieve in next column) is in the lookup.xls as a range named
Unique_MPC_Unit. I cannot get this to work. Is it because the all my
columns contain text of variable widths?

=VLOOKUP(RC[-1],Lookup.xls!Unique_MPC_Unit,5,FALSE)
--
Kay Mejia

This should work fine then

=VLOOKUP(D2,Lookup.xls!Unique_MPC_Unit,2,FALSE)


--
HTH

Bob Phillips

"Kay" wrote in message
...
I changed the references to cell style and now it looks like:
=VLOOKUP(D2,Lookup.xls!Unique_MPC_Unit,5,FALSE)

The data I am searching in is in the 4th column of the current sheet and I
want the results of lookup to be in the 5th column. Here is a sample of
the
current sheet:
CO UNIT ACCOUNT Unique ID MPC Unit Affiliated Unit
1 AL001 2260-0001 1AL0012260-0001 #REF! (affiliated unit is where vlookup
results should be)

This is from the lookup.xls, wyhere unique ID and MPC Unit columns are the
range named Unique_MPC_Unit:

Lawson Acct Lawson Unit Unique ID MPC Unit
1839*0000 1839*0000 HD001

Thanks for your help!
--
Kay Mejia


"Bob Phillips" wrote:

Why are you using R1C1 style not A1

=VLOOKUP(D2,Lookup.xls!Unique_MPC_Unit,5,FALSE)

I cannot see variable widths being the issue, but perhaps you could post
some data.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kay" wrote in message
...
My data has 3 columns that id a value. I have concatenated them to
create
a
unique ID. In the lookup.xls I created a map for each unique ID to
the
desired output text. The uniqueID is in col 4, row2; the range that
contains
my mapping (unique ID in left column, sorted ascending, and text to
assign/retrieve in next column) is in the lookup.xls as a range named
Unique_MPC_Unit. I cannot get this to work. Is it because the all my
columns contain text of variable widths?

=VLOOKUP(RC[-1],Lookup.xls!Unique_MPC_Unit,5,FALSE)
--
Kay Mejia

Thank-you, Ken. Column reference was the last thing wrong and it's all good
now.
--
Kay Mejia


"Ken Wright" wrote:

Your formula should look like this:-

=VLOOKUP(D2,Lookup.xls!Unique_MPC_Unit,2,FALSE)

What this will do is to take the value that is in D2 in your current
workbook, then go to your named range in the file Lookup.xls and look for
that value in the leftmost column of your range (This is column 1 of your
range, regardless of which column it is, eg A, B, C...). When it finds it
it will return the corresponding value from the column to the right of where
it finds the value (From your text I would say this is column 2, hence the 2
in the formula).

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------



"Kay" wrote in message
...
Do I have this backward? I need to find the value located in the
lookup_value by searching in the table_array, returning the 2nd column
from
the array to the column in the col_index_num location.
--
Kay Mejia


"Kay" wrote:

My data has 3 columns that id a value. I have concatenated them to
create a
unique ID. In the lookup.xls I created a map for each unique ID to the
desired output text. The uniqueID is in col 4, row2; the range that
contains
my mapping (unique ID in left column, sorted ascending, and text to
assign/retrieve in next column) is in the lookup.xls as a range named
Unique_MPC_Unit. I cannot get this to work. Is it because the all my
columns contain text of variable widths?

=VLOOKUP(RC[-1],Lookup.xls!Unique_MPC_Unit,5,FALSE)
--
Kay Mejia