Улучшенная формула Vlookup Excel

Question

Я ищу формулу Excel, которую можно использовать для автоматического заполнения E2 на скриншоте ниже. Намерение состоит в том, чтобы найти возраст Марка из столбца B, то есть E2 должно быть 20 лет. Есть идеи, пожалуйста? Я попробовал Match, VLookup и ряд других функций без удачи.

Answer 1

Это почти работает:

{=INDEX(B$2:B$3, MATCH(1, (SEARCH(D2, A$2:A$3)>0)*1, 0), 1)}

Таким образом, ищите D2 в каждой ячейке диапазона A, возвращая 1 для каждой ячейки, в которой он найден, точно совпадая с 1, чтобы вернуть успешные строки, затем индексируйте в диапазон B, используя возвращенную строку, и получите возраст из первый столбец в этом диапазоне. Это предполагает, что совпадение вернет только одну строку. Используйте Ctrl-Shift-Enter, чтобы получить {} указывающий режим массива.

Так как искомое имя может быть префиксом другого имени, мы должны искать слово break (пробел или конец строки) len (name) символов после места, где оно было найдено:

{=INDEX(B$2:B$3,
        MATCH(0,
              LEN(SUBSTITUTE(MID(A$2:A$3,LEN(D2)+SEARCH(D2,A$2:A$3),1),
                             " ",
                             "")),
              0),
        1)}

Мы также должны искать разрыв слова в начале :

{=INDEX(B$2:B$3,MATCH(0,
   LEN(SUBSTITUTE(IFERROR(MID(A$2:A$3,SEARCH(D2,A$2:A$3)-1,1)," ")," ",""))+
   LEN(SUBSTITUTE(MID(A$2:A$3,LEN(D2)+SEARCH(D2,A$2:A$3),1),       " ","")),
   0),1)}

Answer 2

Так как вы попросили ответить и на VBA. Я настроил образец, как ваш:

Используя следующий макрос, я написал:

Public Sub do_stuff_and_things()

Dim i As Integer
Dim name_to_find As String
Dim age As String

name_to_find = Range("'Sheet1'!D2").Value


age = "not found"

i = 2

Do Until i = Range("'Sheet1'!A1").End(xlDown).Row + 1

 If InStr(Range("'Sheet1'!A" & i).Value, name_to_find) Then
 age = Range("'Sheet1'!B" & i).Value
 Exit Do
 End If

 i = i + 1
Loop


Range("'Sheet1'!E2").Value = age
End Sub

Он просто использует функцию InStr, чтобы увидеть, содержит ли поле значение вашего поиска. Конечно, это требует, чтобы имя было напечатано в точности так, как оно указано в столбце поиска.

Надеюсь это поможет.

Если вам нужна помощь в реализации этого, обязательно дайте мне знать.

Answer 3

Вы можете использовать vlookup с подстановочными знаками:

= VLOOKUP("*" & D2 & "*", $ A $ 2:$ B $ 3,2, FALSE)

Где D2 - это ячейка, которую вы хотите найти (которая также может быть жестко закодирована как "Marc"), $ A $ 2:$ B $ 3 - это ваш диапазон, который содержит $ перед каждым столбцом и ссылкой на строку, что сделает диапазон статический, потому что вы, вероятно, не хотите, чтобы ваш диапазон изменился, если вы перетаскиваете эту формулу вниз по столбцу.

2 - это индекс столбца, который вы хотите получить в пределах вашего диапазона, а false означает, что это приближение, которое при использовании в сочетании с вашим подстановочным знаком * будет искать любое значение с Marc в нем. xyzMarc123 вернет значение.