I have a form that has a section at the top were I have a section that shows a contact name in one cell...and then has the contact phone number in another cell.



then it also has a requestor name in a cell and a requestor phone number in a cell.


I wanted to create lists for both the contact names...and requestor names....followed by their phone number. so I could pull the names.

I am able to create the drop down from a range.....but I was wondering if anyone might now of a way or the formula would be used to get the corresponding phone number.

on sheet #2 I would have the table for the info to pull.

column A Column B
1 name 1 number 1
2 name 2 number 2
3 name 3 number 3


I was thinking that in the cell that the phone number would be listed....a formula could be entered that would say something along the lines of

If the (cell with contact name) = Name 1 then enter number 1


Any thoughts on how to do this formula

I don't understand your layout very well but if your table is in, say A2:B10 on sheet 2 you can fetch the number using VLOOKUP like

I am very inexperienced with v look up...but that might work.


basically on Sheet 1 I am going to have a cell that will pull a contact name......from sheet two. and plug it into D6 on Sheet 1

The Name list on sheet to is in column A (1 to whatever)

The Number List is on Sheet 2 in Column B (1 to whatever).....each number should go to the name on that row.....so the name on A1 goes to the number on B1....and so on

So once I select a name to be plugged into D6....I want to have the matching phone number pull to cell H6 on sheet one.


Can you write the correct V look up for that?......and the other question or issue is, that the info on sheet 2 is a list.....and I want D6 to basically just go and find the phone number on sheet 2 and plug it in based on the name that is put in D6

Thanks

Please attach a sample sheet - Thx

I was able to set my sheet up to do what I was wanting with the following Lookup Formula.

=LOOKUP(D4,FMs!A:A,FMs!B:B)


I am having one small glitch though, and wondered if anyone might now how to fix it.


D4 is my cell that provides the information to be looked up. If a name is typed into D4 that can be found in A:A....it provides the correct number for this contact.

the problem is that if a name is typed in that does not match "exactly" to a name listed in A:A....I am still getting a number pulled, and pretty random.

Is there a way to correct this formula.....so that if the data typed in D4 does not match exactly what is in A:A, it will just return an error or some other message ????

The values in lookup_vector ( in your case A:A) must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.

If the values aren't sorted, and INDEX/MATCH combination can be used. As previously requested, a copy of the workbook to work with would be helpful, though.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]