Lookup Last name column, return Last and First Name

[jmh2008]

My spreadsheet has first and last name in two columns. I want to create a data validation the will look up both first and last name together, because there may be more than one John Smith, so just looking up Smith does not work.

If I create a separate sheet and do concatenation, and then update the list from the original sheet, I need to have the list update.

thanks for your help,
Juanita

 

[mikerickson]

You could use a validation formula like
=(1=SUMPRODUCT(--($A5=$A$1:$A$10)*($B5=$B$1:$B$10)))

 

[jmh2008]

Sorry, I don't understand how this works.

I want to have a Validation cell that if you search Smith, you may see Smith John, Smith Joe, Smith Mary, and you can select Smith Mary. I don't know VBA, so that's no an option.

 

[mikerickson]

This can be done with named ranges.
If you have last names in Sheet1!A1:A10 with the first names in column B.
And your entry cell is Sheet1!$D1 (as below)

Name: aRange RefersTo: =Sheet1!$A$1:$A$6 (can be dynamicly defined)
Name: bColumn RefersTo: =Sheet1!$B:$B (note is entire column)

the next name uses relative referencing and should be defined relative to Sheet1!E1
Name: other
RefersTo: =INDEX(Sheet2!1:1, 1, 1):INDEX(Sheet2!1:1, 1, COUNTIF(aRange,Sheet1!$D1))

In Sheet2! A1:Z1, put the array formula
=INDEX(bColumn, LARGE(TRANSPOSE((aRange=Sheet1!$D1)*ROW(aRange)), COLUMN(A:Z)), 1)
(entered with Ctrl-Shift-Enter (Cmd+Return for Mac))

Then Validation on Sheet1!E1 with a list source =other will give the list Francis, Betty, Adam.

<table border=1 cellspacing=0>
<tr align="center" bgcolor=#A0A0A0><td width=25> <td width=25><b>A</b><td width=25><b>B</b><td width=25><b>C</b><td width=25><b>D</b><td width=25><b>E</b></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>1</b><td align="left" bgcolor=#FFFFFF>Smith<td align="left" bgcolor=#FFFFFF>Adam<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>Smith<td align="left" bgcolor=#FFFFFF>Betty</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>2</b><td align="left" bgcolor=#FFFFFF>Smith<td align="left" bgcolor=#FFFFFF>Betty<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>3</b><td align="left" bgcolor=#FFFFFF>Jones<td align="left" bgcolor=#FFFFFF>Carl<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>4</b><td align="left" bgcolor=#FFFFFF>Jones<td align="left" bgcolor=#FFFFFF>Dianna<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>5</b><td align="left" bgcolor=#FFFFFF>Williams<td align="left" bgcolor=#FFFFFF>Edward<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>6</b><td align="left" bgcolor=#FFFFFF>Smith<td align="left" bgcolor=#FFFFFF>Francis<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
</table>