Я ищу формулу excel, которую я могу использовать для автоматического заполнения E2 на скриншоте ниже. Намерение состоит в том, чтобы найти возраст Марка из столбца B, то есть E2 должен быть 20. Есть идеи, пожалуйста? Я пробовал матч, VLookup и ряд других функций, не повезло.
Усиленная Формула ВПР В Excel
3 ответа
Это почти работает:
{=INDEX(B:B, MATCH(1, (SEARCH(D2, A:A)>0)*1, 0), 1)}
то есть, поиск D2 в каждой ячейке диапазона A, возвращая 1 для каждой ячейки, где он найден, матч на 1 точно, чтобы вернуть успешные строки, а затем индекс в диапазоне B с помощью строки возвращается, и получить возраст из первого столбца в этом диапазоне. Это предполагает, что матч будет возвращать только одну строку. Используйте Ctrl-Shift-Enter, чтобы получить {}, указывающий режим массива.
Так как имя, которое мы ищем, может быть префиксом другого имени, мы должны искать разрыв слова(пробел или конец строки) len (имя) символов после того, где он был найден:
{=INDEX(B:B,
MATCH(0,
LEN(SUBSTITUTE(MID(A:A,LEN(D2)+SEARCH(D2,A:A),1),
" ",
"")),
0),
1)}
мы должны искать слово перерыв в начало тоже:
{=INDEX(B:B,MATCH(0,
LEN(SUBSTITUTE(IFERROR(MID(A:A,SEARCH(D2,A:A)-1,1)," ")," ",""))+
LEN(SUBSTITUTE(MID(A:A,LEN(D2)+SEARCH(D2,A:A),1), " ","")),
0),1)}
Так как вы попросили ответ 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, чтобы увидеть, если поле содержит значение поиска. Конечно, это требует, чтобы имя было введено точно так, как оно есть в столбце поиска.
надеюсь, что это помогает.
Если вам нужна помощь в реализации этого, безусловно, дайте мне знать.
вы можете использовать ВПР с шаблонами:
=VLOOKUP ("*"&D2&"*", $A$2:$B$3,2,FALSE)
где D2-ячейка, которую вы хотите найти (которая также может быть жестко закодирована как "Marc"), $A$2:$B$3-это ваш диапазон, который содержит $ перед каждой ссылкой на столбец и строку, что сделает диапазон статическим, потому что вы, вероятно, не хотите, чтобы ваш диапазон менялся, если вы перетаскиваете эту формулу вниз по столбцу.
2-индекс столбца, который требуется получить в пределах вашего диапазона и false представляет, что это приближение, которое при использовании в сочетании с вашей дикой карты *, будет искать любое значение с Марком в нем. xyzMarc123 будет возвращать значение.
Постоянная ссылка на данную страницу: [ Скопировать ссылку | Сгенерировать QR-код ]