VLookup Problem
 

I'm using a VLOOKUP function to pull up a clientName from a 2nd
worksheet and apply it to the 1st worksheet (tab named Book2). My 2nd
worksheet (tab named ClientDB3) contains a client DB with the ClientID
and ClientName. The VLOOKUP fx is used by the 1st worksheet which is a
client report basically.

I have a column on the report page (1st worksheet) that has a list in a
dropdown box that fills with the clientID from the 2nd worksheet as they
are added to the Client DB (2nd worksheet). Both the ClientID and the
ClientName columns are defined to grow as entries are added in the 2nd
worksheet. I have a list dropdwon data validation defined for both
columns in the 1st sheet, could this be the problem???? Should I
eliminate the dropdown for the Name and only have the =VLOOKUP fx
used.

In book2 1st worksheet when I pick a ClientID from the dropdown box, I
would like the ClientName to automatically fill in adj column.

My fomula is:

=VLOOKUP(A8,ClientName,1,FALSE)

which is placed in the column adj to A8 (the ClientID column)


It returns a #NA value.


--
bwall
------------------------------------------------------------------------
bwall's Profile: http://www.excelforum.com/member.php...o&userid=26997
View this thread: http://www.excelforum.com/showthread...hreadid=466357

bwall Wrote:
I'm using a VLOOKUP function to pull up a clientName from a 2nd
worksheet and apply it to the 1st worksheet (tab named Book2). My 2nd
worksheet (tab named ClientDB3) contains a client DB with the ClientID
and ClientName. The VLOOKUP fx is used by the 1st worksheet which is a
client report basically.

I have a column on the report page (1st worksheet) that has a list in a
dropdown box that fills with the clientID from the 2nd worksheet as they
are added to the Client DB (2nd worksheet). Both the ClientID and the
ClientName columns are defined to grow as entries are added in the 2nd
worksheet. I have a list dropdwon data validation defined for both
columns in the 1st sheet, could this be the problem???? Should I
eliminate the dropdown for the Name and only have the =VLOOKUP fx
used.

In book2 1st worksheet when I pick a ClientID from the dropdown box, I
would like the ClientName to automatically fill in adj column.

My fomula is:

=VLOOKUP(A8,ClientName,1,FALSE)

which is placed in the column adj to A8 (the ClientID column)


It returns a #NA value.

ASSUME that in your worksheet 2 (ClientDB3),

1. ClientID is A1:A100 and
2. ClientName is B1:B100

So, your formula in worksheet 1 is

=VLOOKUP(A8,CLIENTDB3!$A$1:$B$100,2,0)

Hope this will help you.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=466357

I have the columns for the ID and Name in ClientDB3 sert for a dynamic
range on both columns. The define name is ClientID for ID and
ClientName for name.

I tried setting VLOOKUP to:

=VLOOKUP(A8,ClientDB3!ClientName,1,FALSE) or
Book2!ClientName

but that didn't work

I thought if you had a range name you could subsitute that for the
cells or rows.


I need to have a dynamic range because entries will constantly be added
to the client DB.


--
bwall
------------------------------------------------------------------------
bwall's Profile: http://www.excelforum.com/member.php...o&userid=26997
View this thread: http://www.excelforum.com/showthread...hreadid=466357

As long as all sheets are in the same workbook, you don't need to
identify a range with the sheet name.

My guess is the values in the dropdown box do NOT match those in A8,
etc.

Try changing your formula by removing the FALSE and making it TRUE.
What do you get now? If you get either the first or last name in the
list, you can be confident that either the spellings or spaces are not
the same in both places.

Just a thought.


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=466357

But if the OP did have sheet level names on each sheet, then that worksheet name
would be required.



swatsp0p wrote:

As long as all sheets are in the same workbook, you don't need to
identify a range with the sheet name.

My guess is the values in the dropdown box do NOT match those in A8,
etc.

Try changing your formula by removing the FALSE and making it TRUE.
What do you get now? If you get either the first or last name in the
list, you can be confident that either the spellings or spaces are not
the same in both places.

Just a thought.

--
swatsp0p

------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=466357

--

Dave Peterson

If you select the clientDB3 worksheet and then hit Edit|Goto and type:
ClientName
do you select the range you expect?

And if you eyeball that column 1 of that range and pick out the cell that you
think matches and use:

=a8=clientdb3!x99
(x99 is the cell that holds that match)

Do you get true or false back.

Along with Swatsp0p's warning about extra spaces in the cell, excel will see a
difference between numeric values and text values (123 < '123).

If you change the format (to general or to Text), then reenter the value, does
it work ok?

bwall wrote:

I have the columns for the ID and Name in ClientDB3 sert for a dynamic
range on both columns. The define name is ClientID for ID and
ClientName for name.

I tried setting VLOOKUP to:

=VLOOKUP(A8,ClientDB3!ClientName,1,FALSE) or
Book2!ClientName

but that didn't work

I thought if you had a range name you could subsitute that for the
cells or rows.

I need to have a dynamic range because entries will constantly be added
to the client DB.

--
bwall
------------------------------------------------------------------------
bwall's Profile: http://www.excelforum.com/member.php...o&userid=26997
View this thread: http://www.excelforum.com/showthread...hreadid=466357

--

Dave Peterson