Lookup function or Index/Match Combo?

[Slinger16]

I'm having issues solving a problem. (Excel 07, Windows XP)

I have a Table that is in this format:

Jerry Miller Corporation
101 Carter Ave.
Minneapolis, MN 72821

What formula do I use to Lookup the person's name and then return only the street address?

Edit: The first name in the top row is supposed to be in its own column.

 

[PaddyD]

hi - welcome to the board.

should be able to sort this out, but might need more info on your data setup.

e.g. do you have one long column of data with always 3 lines for each entry:

name
street
city

...and given name you want street, or are there variations in the data set up?

if it's regular as described above then with data in col a and name to lookup in b1 it's just:

=index($a41:$a$100,match(b1,$a$1:$a$100,0)+1)


...if not, then post back with more details re set up