Formula for looking the more nearest value

[jamiguel77]

hi friends...


i have this data:

A1=Miguel Enriquez
A2=Jhon Smith Award
A3=Kevin Ramirez Torres
A4=Maria Rosas Torres
A5=Juan Carlos Rosas

if the user try looking: 'Kevin' i want return the value A3:Kevin Ramirez Torres or if try looking: Carlos: i want return: A5=Juan Carlos Rosas also if try find: Karlos would return too: A5=Juan Carlos Rosas or if try looking: Cmit, would return: A2=Jhon Smith Award or if try looking: Cevin: A3=Kevin Ramirez Torres

its possible?
if yes how to?

thanks

 

[cyrilbrd]

Interesting, just curious if someone will come up with a solution without VBA.
Wildcards could be used, but your query allows way too many variables:

	A	B	C	D
1	Miguel Enriquez
2	Jhon Smith Award
3	Kevin Ramirez Torres
4	Maria Rosas Torres
5	Juan Carlos Rosas
6
7
8	query	Kevin	Smith	Rosas
9	result	3	2	5

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B9 =MATCH(B8&"*",A1:A5,0) C9 =MATCH("*"&C8&"*",A1:A5,0) D9 =MATCH("*"&D8,A1:A5,0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Wanderer_geologist]

Hello

Almost everything is possible, if you have a need to just find text, perhaps the CTRL+H search will be enough, in advance mode, it provide the position of record matching up the criteria.

With code, I made a simple set up.

Sub FindMyStr()

Dim Ws As Worksheet: Set Ws = ActiveSheet
Dim ranSource, ranFindCell As Range
Dim QueryTest As Variant: QueryTest = InputBox("Please Capture Name to find:")


Set ranSource = Range("A1:A100")
Set ranFindCell = ranSource.Find(QueryTest, , xlValues, xlPart)

' it is set with XlPart, so it will look for partial match and stop at the first match. For exact match up, use xlWhole.

If Not ranFindCell Is Nothing Then MsgBox " The record is in position A" & ranFindCell.Row


End Sub