Усиленная Формула ВПР В Excel

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

enter image description here

9
задан lisa17
29.11.2022 12:47 Количество просмотров материала 2542
Распечатать страницу

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)}
2
отвечен Nick Russo 2022-11-30 20:35

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

enter image description here

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

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, чтобы увидеть, если поле содержит значение поиска. Конечно, это требует, чтобы имя было введено точно так, как оно есть в столбце поиска.

надеюсь, что это помогает.

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

1
отвечен Eric F 2022-11-30 22:52

вы можете использовать ВПР с шаблонами:

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

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

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

1
отвечен CdeoEm 2022-12-01 01:09

Постоянная ссылка на данную страницу: [ Скопировать ссылку | Сгенерировать QR-код ]

Ваш ответ

Опубликуйте как Гость или авторизуйтесь

Имя
Вверх