Names with If function

  • Thread starter Thread starter Dgwood90
  • Start date Start date
D

Dgwood90

I have created a drop down list of names. I am now trying to create an IF
function, that when a certain name is selected from the drop down box, it
will populate with a phone number. I receive the #NAME error. Can I not use
names with IF function?
 
If there are only a "few" selections available from your drop down list then
you can use an IF formula like this:

A1 = drop down list

=IF(A1="Biff","BR-549",IF(A1="Joe","555-1212",IF(A1="Linda","123-4567","")))

If you have more than a "few" selections available then your best bet is to
create a 2 column table like this:

.........AA.............AB......
1.....Biff..........BR-549
2.....Joe..........555-1212
3.....Linda......123-4567

Then use a lookup formula like this:

=VLOOKUP(A1,AA1:AB3,2,0)

The list of names in column AA can also be the source of your drop down
list.
 
If you constructed a list of Names and Phone Numbers (in that order) on
another sheet, you could use the Names in the Data Validation list and use a
VLOOKUP function to return the Phone Number for the selected name.

Here's an example:

On Sheet2
Cells A2:B10 contain a list of Names and Phone Numbers.
Assign a Range Name to that range
.. Select A2:B10
.. In the Name Box (just above the Col_A heading) type: NamesPhones
.. Press [ENTER]
.. Select A2:A10
.. In the Name Box (just above the Col_A heading) type: NameList
.. Press [ENTER]

On Sheet1
Create a Data Validation in Cell A2
.. Select A2
.. From the Excel Main Menu: <data><validation>
....Allow: List
....Source: NameList
....Click the [OK] button

Select B2
.. Enter this formula in that cell: =IF(A2="","",VLOOKUP(A2,NamesPhones,2,0))

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
This is the formula I am using.
=IF(A10=Steve Smith,555-555-5555,IF(A10=John Adams,555-555-5555,"")). I am
using four names.
 
Try something like this (in sections for readability):

=IF(A10="","",INDEX({"555-1111","555-2222","555-3333","555-4444"},
MATCH(A10,{"Alpha","Bravo","Charlie","Delta"},0)))

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
hi,
the only thing i see wrong with your formula is the names and phone numbers
are not enclosed in double quotes.
=IF(A10="Steve Smith","555-555-5555",IF(A10="John Adams","555-555-5555","")
text should always be enclosed in double quotes. and telephone number are
considered text. here is a formula i developed as a test.
=IF(B3="Bob",C4,IF(B3="cary",C5,IF(B3="jim",C6,IF(B3="lucy",C7,""))))
here i called the cell that had the phone number in it.
I used data validation for my drop down.

post back if you have problems.
regards
FSt1
 
I can give it a try. Thanks

Ron Coderre said:
Try something like this (in sections for readability):

=IF(A10="","",INDEX({"555-1111","555-2222","555-3333","555-4444"},
MATCH(A10,{"Alpha","Bravo","Charlie","Delta"},0)))

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
Click to expand...
 
Back
Top