Select a value from one list, presents outcome from two lists

S

schwin

I'm kind of new to this excel-thing, and i'm sorry if my questions seem
childish and terribly annoying.

Anyway I have two list one called names, and one calle
telephonenumbers. These are placed in sheet 2. In sheet 1 i've create
a drop-down list with the data-validation-list etc and I've told th
list to drop-down is NAMES. Based on the names that appear i want th
telephone-nr that "belongs" to that name to show up to the right of th
name in the adjoining cell (these numbers are written i
TELEPHONENUMBERS). Could someone please help me with this problem??

Regard
 
T

Tom Ogilvy

=Vlookup(A1,Sheet1!$A:$B,2,False)

where A1 is on sheet2 and contains the results of the dropdown selection
Assumes names and phone numbers are on Sheet1 in columns A and B
respectively. You can change A:B to a specific range such as

Sheet1!$A$1:$B$100

you can keep it from displaying #N/A by adding

=if(A1="","",Vlookup(A1,Sheet1!$A:$B,2,False))
 
B

Bob Phillips

Assuming DV in A1, in the adjacent cell

=INDEX(TELEPHONENUMBERS,MATCH(A1,NAMES,0))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
S

schwin

Thank you for your reply. I must say I really appriciate it, but I ge
an error message saying that there is an error in the formula. In shee
2, I create the dropdown from sheet 1 containing the names. Then
paste the formula into B1 in sheet 2, and this is supposed to work??
don't have to do any modifications in the formula??

Again thank you for replying and sry for my lack of competence when i
comes to excel..
 
T

Tom Ogilvy

I set up a sheet as you describe/I described in my assumptions and tried
both my formulas in cell B1 of Sheet2. Cell A1 had the dropdown.

Both worked being pasted from the posting (no modification). Of course your
set up would have to match or you would need to modify them. Also, my
dropdown list referenced the source names in Sheet1, so I know there will be
a match. Not sure if your dropdown does that or not. If there is no match,
the formula will return #N/A.

It is impossible to tell whether you are replying to Bob or replying to me,
so you need to specify what you are referencing.
 
S

schwin

That one did the trick, just had to get my friend to explan it to me
and then I got the trick, and now I fell truly good about myself :
Thank you for quick reply!!

schwi
 
Top