Vlookup assistance

[liverebel]

If you look at the example sheet I have, it will return the address for the name entered into A2. What I would like is to be able to just type in the first name and have it return the address as if I had typed in the whole name. Is this possible?

[NBVC]

What if there are multiple people with the same first name?

Have you considered using a data validation drop down list so that the user can just pick a name from the list: Data Validation Intro

[liverebel]

What if there are multiple people with the same first name?

Have you considered using a data validation drop down list so that the user can just pick a name from the list: Data Validation Intro

There will no be duplicate names as this is just a mock up of what I'm doing. The first word in the "name" field will be unique to that entry. I was originally going to go with the Data Val List, but found it take more time sorting through 300 unique "names" than just typing it the first part of the entry. If there is no other way to do it I may have to use the data validation list.

[NBVC]

Well you can use a wildcard in the Vlookup, i.e. VLOOKUP(A2&"*",Ref!$A$1:$C$5,2,FALSE)

this will look to see if anything in column A of the Ref! table starts with name in A2...

[liverebel]

Well you can use a wildcard in the Vlookup, i.e. VLOOKUP(A2&"*",Ref!$A$1:$C$5,2,FALSE)

this will look to see if anything in column A of the Ref! table starts with name in A2...

Ok great that is a start to what I want....the problem is if I leave it blank it pulls in the first address off the list. I am assuming I can remedy this by taking by list worksheet and putting a blank entry at the top.

EDIT*
Ok adding a blank line didnt work. How can I get it to show blanks on the address line if the entry line is also blank?

[NBVC]

You could do:

=IF(A2="","",VLOOKUP(A2&"*",Ref!$A$1:$C$5,2,FALSE))

you could also nest your error trap formula in place of the simple VLOOKUP

[liverebel]

You could do:

=IF(A2="","",VLOOKUP(A2&"*",Ref!$A$1:$C$5,2,FALSE))

you could also nest your error trap formula in place of the simple VLOOKUP

Hmm this looks great, but it keeps returning an error and I dont see why?

[NBVC]

Please see attached.

[Haseeb Avarakkan]

Also, try

main sheet A3, copy down

=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",VLOOKUP(A$2&"*",Ref!$A:$C,ROWS(A$3:A3)+1,0)))