A way to search by name or number in a data validation list and only return the name in that cell and the associated number in the adjacent cell?

[Lisenbe]

Is there a way to look up a name OR number in a data validation list but only let the user select the number and then have the associated number populate in the adjacent cell?
My goal is for users in a file to be able to see the project numbers and/or names (some users search by names and some by numbers) but only populate column A with numbers and column B with names.

I've tried data validation lists and xlookup but I cannot figure out how to let the users search A1 by both project number and project name and only select the project name.
I also would prefer no code if possible.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Below is one way (but it does require code )

1. Combine the numbers and names into a single column as I have done in col H.
2. Set up the Data Validation as shown below the mini sheet (I have applied it to A2:A10)
3. Use the Worksheet_Change event code shown (If you need instructions for how to do that, post back)

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
 
  Set Changed = Intersect(Target, Range("A2:A10"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If Len(c.Value) > 0 Then
        c.Resize(, 2).Value = Split(c.Value, "|")
      Else
        c.Offset(, 1).ClearContents
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

Here is my sheet after I have chosen a few items from the list

Lisenbe.xlsm
	A	B	C	D	E	F	G	H
1	Number	Number				Number	Name	DV List
2						1	happy street	1|happy street
3	2	sad house				2	sad house	2|sad house
4						3	wet road	3|wet road
5						4	burning bridge	4|burning bridge
6	4	burning bridge				5	desk work	5|desk work
7	5	desk work
8
9	1	happy street
10
Sheet1

Cell Formulas
Range	Formula
H2:H6	H2	=F2&"|"&G2

Cells with Data Validation
Cell	Allow	Criteria
A2:A10	List	=$H$2:$H$6

 

[SunnyAlv]

Like This ? (With Formula)

Row "B2"

=IFERROR(IF(A2="","",XLOOKUP(A2,F:F,G:G)),"")