Making lists...

[Max30]

I have four columns containing about 200 addresses.

A Names
B Streets
C Cities
D Phone no.

If I write a name in D1 I want to get the corresponding street, city and phone no. in E1, F1 and G1.
I also want to copy the formula downwards let's say ten times so I can make a list.
If I for example just want to make a list of three names (D1-D3) I want the rest of the fields to stay blank.

Can anybody help me please?

Thank you!

[teylyn]

Hi,

you say you have data in columns A to D. Then you say you want to enter a name in D1. Does that mean you will overwrite the phone number?

I'd suggest that you may want to start your new data area in column E. Write the name in cell E1. Then use something like

=vlookup(E1,$A$1:$E$100,2,false) for the street in F1
=vlookup(E1,$A$1:$E$100,3,false) for the city in G1
etc.

[Max30]

Thanks, it worked out at last!
The problem was that I had to change all commas to semicolon because of my Swedish version of Excel...
One other thing, if I leave a "search field" (E1) emty I want it to stay blank, instead of saying N/A.
Can that be solved?

Thank you very much!

[teylyn]

yes, you can use IFERROR for that

=iferror(=vlookup(E1,$A$1:$E$100,2,false),"")

[Max30]

Thank you very much, it worked, I just to change the commas to semicolon as before.

Now I'll check out my other "problem" in the Excel Programming forum, something for you perhaps?

Have a nice weekend!

/Max

[teylyn]

Max, glad it worked for you.

If you work with the Swedish version of Excel, you will always need to use the semicolon as the separator symbol between formula parameters. Most people here are probably on the English or US version, both of which use the comma.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[Max30]

Yes, you learn something new every day
I'll go and mark this thread as solved.

Thanks for your help!

//Max