VLOOKUP? Need to have value returned when select from drop-down list

B

Budamon

Hello, I work on a help desk in a call center and we have a nifty
worksheet where we can select the agent who calls us from a drop-down
list. The problem is, we also have to select the supervisor from
another drop-down list. I have been doing research on how to stream
line this process by having the supervisor's name automatically
displayed when we select the the agent's name from the drop-down list.
Unfortunately, the guy who designed this worksheet no longer is with the
company. I have already modified it to give statistics on the reps who
call, but i'm trying to stream line the entering process. I know how
to make drop-down lists and everything, but i'm having trouble with the
returning value part. After some research, it looks like the VLOOKUP
function will be the best bet, but i don't understand all of the
arguments you have to enter. My data looks like the following:

Worksheets: Entered!, Data!

on Data!:
A B
AgentName1 Supervisor1
AgentName2 Supervisor2
AgentName3 Supervisor1
AgentName4 Supervisor3 etc.

on Entered!, I want the following to happen:
A B
(when select) AgentName1 (want it to return) Supervisor1
(when select) AgentName4 (want it to return) Supervisor3 etc.

From what I have found, the formula needs to look something like this:
On Entered!:
A B
=VLOOKUP(A1, Data!A1:B1, 2, false)

i'm sure you know why i'm getting the #NA error returned instead.

I don't understand what the 2 or the false does and that might be the
problem, though i'm sure it also has something to do with my cell
references. Does anyone have any idea on how to get this to work?

My second question is how do you get a default value to be displayed
until you make a selection from the drop down list? The way I have my
new sheet setup, it's blank until you make a selection. Any and all
help will be greatly appreciated!!

Thanks!

Curiously yours,
Andrew
 
B

Biff

Hi!

About Vlookup.........
=VLOOKUP(A1, Data!A1:B1, 2, false)

i'm sure you know why i'm getting the #NA error returned instead.
Click to expand...

The first argument, A1, is the lookup_value. This is the value you want to
lookup and the place you want to look for that value is the table_array
which is the second argument, Data!A1:B1. The lookup_value is searched for
in the leftmost column of the table_array. If the lookup_value is found you
have to tell the function what column of the table_array contains the value
to return that corresponds to the lookup_value. This is the third argument,
col_index_num. In your formula the col_index_num is 2. The col_index_num is
relative to the table_array. If the table_array range was Z1:AA10 the
col_index_num would still be 2 because it's the second column in relation to
the table_array even though it's physical location in the worksheet is
column 27.

The fourth argument is range_lookup. This argument is optional and if
ommited, defaults to TRUE. This argument is used to tell the function if the
table_array is sorted or not sorted for the purpose of finding either an
exact match or the closest match. TRUE if it's sorted, FALSE if it's not
sorted. You would use an argument of FALSE if you want to look for an exact
match. You would use TRUE if you want the closest match that is less than
the lookup_value if there is no exact match. Using the TRUE argument
requires that the table_array be sorted in ascending order to work properly.
The TRUE argument is used mostly for numeric ranges but also works for text.

OK, let's try to figure out why your formula is returning an error.
=VLOOKUP(A1, Data!A1:B1, 2, false)
Click to expand...

As written, the table_array consists of only 2 cells and one of those is
supposed to contain the lookup_value. You have to increase the size of your
table_array to match the range size of your table:
AgentName1 Supervisor1
AgentName2 Supervisor2
AgentName3 Supervisor1
AgentName4 Supervisor3
Click to expand...

Based on that table it would be:

=VLOOKUP(A1, Data!A1:B4, 2, false)

I'm assuming your drop down list is in cell A1 which is the lookup_value. If
there is no selection made from the drop down and cell A1 is empty then the
formula will return #N/A because there is no matching empty cell in the
table_array. If there is a selection made from the drop down but that value
does not exist in the table_array (using the FALSE 4th argument) then the
formula will return an #N/A error.

You can build into the formula tests that will check for both of these
conditions and define some value to return instead of the #N/A error. To do
this we need to use an IF function.

The most popular method to do this is:

=IF(ISNA(VLOOKUP(A1,Data!A1:B4, 2, false)),"",VLOOKUP(A1, Data!A1:B4, 2,
false))

This formula does a first lookup and if the result is #N/A, returns a blank
cell. If the first lookup does not result in #N/A then it does a second
lookup and returns the the appropriate value.

Biff
 
B

Budamon

Biff, thank you SO much! ever consider teaching as a career? :) I
can't wait to get to work (did i just say that?!?) and try it out.
Thanks again and keep up the great work!

Regards,
 
Top