Возврат нескольких строк с различными данными столбцов с помощью Vlookup или INDEX-MATCH

Теперь у меня есть файл excel с 2 листами, мне нужно вытащить некоторые данные из листа 1 в лист 2, на основе одного критерия — текст в столбце A из листа 1 содержит определенную текстовую строку из A2 из листа 2. В то же время, я также должен извлечь данные из других столбцов из тех строк, где находится текстовая строка. Однако Vlookup возвращает только первый экземпляр. Так как файл является конфиденциальным, я создал moackup для этого.

Лист1

Classes Taken   Name    Gender  Age

Math,Physics,Chemistry, Literature  Joseph  Male    16

Chinese, History, Philosophy, Politics  Alice   Female  17

Philosophy, Math, Physics   Erica   Female  16

Chinese, Biology, Statistics    Evan    Male    18

Лист2

Philosophy  (Lookup)

Name    Gender  Age

Что делать, если я хочу, чтобы информация о людях, которые приняли философию класс отображается в sheet2.

Спасибо!!

2 ответа

  1. РЕШЕНИЕ БЕЗ МАССИВА

    Лист1
    — Введите данные таблицыSheet1, как показано здесь:
    Sheet1 миниатюра изображения

    Лист2
    — Ячейка A1содержит значение поиска: философия .
    — Ячейки A2:C2содержат данные строки заголовка: Name Gender Age
    — В ячейку A3вставьте следующую формулу:
    =INDEX(Sheet1!$A2:$D2,MATCH("*"&$A

    NON-ARRAY SOLUTION

    Sheet1
    - Enter table data on Sheet1 as shown here:
    Sheet1 Thumbnail Image

    Sheet2
    - Cell A1 contains the lookup value: Philosophy.
    - Cells A2:C2 contain the header row data: Name Gender Age
    - In cell A3, insert this formula:
    =INDEX(Sheet1!$A2:$D2,MATCH("*"&$A$1&"*",Sheet1!$A2:$A2,0),MATCH(A$2,Sheet1!$A$1:$D$1,0))
    - Press ENTER.
    - Copy across to C3, then down to C6 to get the result shown here:
    Sheet2 Thumbnail Image

    ARRAY SOLUTION

    Sheet1
    - Enter table data on Sheet1 as shown here:
    Sheet1 Thumbnail Image

    Sheet2
    - Cell A1 contains the lookup value: Philosophy.
    - Cells A2:C2 contain the header row data: Name Gender Age
    - In cell A3, insert this formula:
    =IFERROR(INDEX(Sheet1!B$2:B$5,SMALL(IF(ISNUMBER(SEARCH($A$1,Sheet1!$A$2:$A$5)),ROW(Sheet1!B$2:B$5)-ROW(Sheet1!B$2)+1),ROWS(Sheet1!$1:1))),"")
    - Press CTRL+SHIFT+ENTER.
    - Copy across to C3, then down to C6 to get the result shown here:
    Sheet2 Thumbnail Image

    Please Note
    - This formula contains the IFERROR function, which will only work in Excel for PC 2007 or newer. It will also work in Excel for Mac 2011 or newer (according to Microsoft's documentation).
    - This formula is also an ARRAY, which may slow things down if you're working with a large dataset.
    - Each time you edit this formula, reconfirm by pressing CTRL+SHIFT+ENTER.

    &"*",Sheet1!$A2:$A2,0),MATCH(A,Sheet1!$A

    NON-ARRAY SOLUTION

    Sheet1
    - Enter table data on Sheet1 as shown here:
    Sheet1 Thumbnail Image

    Sheet2
    - Cell A1 contains the lookup value: Philosophy.
    - Cells A2:C2 contain the header row data: Name Gender Age
    - In cell A3, insert this formula:
    =INDEX(Sheet1!$A2:$D2,MATCH("*"&$A$1&"*",Sheet1!$A2:$A2,0),MATCH(A$2,Sheet1!$A$1:$D$1,0))
    - Press ENTER.
    - Copy across to C3, then down to C6 to get the result shown here:
    Sheet2 Thumbnail Image

    ARRAY SOLUTION

    Sheet1
    - Enter table data on Sheet1 as shown here:
    Sheet1 Thumbnail Image

    Sheet2
    - Cell A1 contains the lookup value: Philosophy.
    - Cells A2:C2 contain the header row data: Name Gender Age
    - In cell A3, insert this formula:
    =IFERROR(INDEX(Sheet1!B$2:B$5,SMALL(IF(ISNUMBER(SEARCH($A$1,Sheet1!$A$2:$A$5)),ROW(Sheet1!B$2:B$5)-ROW(Sheet1!B$2)+1),ROWS(Sheet1!$1:1))),"")
    - Press CTRL+SHIFT+ENTER.
    - Copy across to C3, then down to C6 to get the result shown here:
    Sheet2 Thumbnail Image

    Please Note
    - This formula contains the IFERROR function, which will only work in Excel for PC 2007 or newer. It will also work in Excel for Mac 2011 or newer (according to Microsoft's documentation).
    - This formula is also an ARRAY, which may slow things down if you're working with a large dataset.
    - Each time you edit this formula, reconfirm by pressing CTRL+SHIFT+ENTER.

    :$D

    NON-ARRAY SOLUTION

    Sheet1
    - Enter table data on Sheet1 as shown here:
    Sheet1 Thumbnail Image

    Sheet2
    - Cell A1 contains the lookup value: Philosophy.
    - Cells A2:C2 contain the header row data: Name Gender Age
    - In cell A3, insert this formula:
    =INDEX(Sheet1!$A2:$D2,MATCH("*"&$A$1&"*",Sheet1!$A2:$A2,0),MATCH(A$2,Sheet1!$A$1:$D$1,0))
    - Press ENTER.
    - Copy across to C3, then down to C6 to get the result shown here:
    Sheet2 Thumbnail Image

    ARRAY SOLUTION

    Sheet1
    - Enter table data on Sheet1 as shown here:
    Sheet1 Thumbnail Image

    Sheet2
    - Cell A1 contains the lookup value: Philosophy.
    - Cells A2:C2 contain the header row data: Name Gender Age
    - In cell A3, insert this formula:
    =IFERROR(INDEX(Sheet1!B$2:B$5,SMALL(IF(ISNUMBER(SEARCH($A$1,Sheet1!$A$2:$A$5)),ROW(Sheet1!B$2:B$5)-ROW(Sheet1!B$2)+1),ROWS(Sheet1!$1:1))),"")
    - Press CTRL+SHIFT+ENTER.
    - Copy across to C3, then down to C6 to get the result shown here:
    Sheet2 Thumbnail Image

    Please Note
    - This formula contains the IFERROR function, which will only work in Excel for PC 2007 or newer. It will also work in Excel for Mac 2011 or newer (according to Microsoft's documentation).
    - This formula is also an ARRAY, which may slow things down if you're working with a large dataset.
    - Each time you edit this formula, reconfirm by pressing CTRL+SHIFT+ENTER.

    ,0))
    — Press ENTER.
    — Скопируйте поперекC3, а затем внизC6, чтобы получить результат, показанный здесь:
    Sheet2 эскиз изображения

  2. РЕШЕНИЕ МАССИВА

    Лист1
    — Введите данные таблицыSheet1, как показано здесь:
    Sheet1 миниатюра изображения

    Лист2
    — Ячейка A1содержит значение поиска: философия .
    — Ячейки A2:C2содержат данные строки заголовка: Name Gender Age
    — В ячейку A3вставьте следующую формулу:
    =IFERROR(INDEX(Sheet1!B:B,SMALL(IF(ISNUMBER(SEARCH($A

    NON-ARRAY SOLUTION

    Sheet1
    - Enter table data on Sheet1 as shown here:
    Sheet1 Thumbnail Image

    Sheet2
    - Cell A1 contains the lookup value: Philosophy.
    - Cells A2:C2 contain the header row data: Name Gender Age
    - In cell A3, insert this formula:
    =INDEX(Sheet1!$A2:$D2,MATCH("*"&$A$1&"*",Sheet1!$A2:$A2,0),MATCH(A$2,Sheet1!$A$1:$D$1,0))
    - Press ENTER.
    - Copy across to C3, then down to C6 to get the result shown here:
    Sheet2 Thumbnail Image

    ARRAY SOLUTION

    Sheet1
    - Enter table data on Sheet1 as shown here:
    Sheet1 Thumbnail Image

    Sheet2
    - Cell A1 contains the lookup value: Philosophy.
    - Cells A2:C2 contain the header row data: Name Gender Age
    - In cell A3, insert this formula:
    =IFERROR(INDEX(Sheet1!B$2:B$5,SMALL(IF(ISNUMBER(SEARCH($A$1,Sheet1!$A$2:$A$5)),ROW(Sheet1!B$2:B$5)-ROW(Sheet1!B$2)+1),ROWS(Sheet1!$1:1))),"")
    - Press CTRL+SHIFT+ENTER.
    - Copy across to C3, then down to C6 to get the result shown here:
    Sheet2 Thumbnail Image

    Please Note
    - This formula contains the IFERROR function, which will only work in Excel for PC 2007 or newer. It will also work in Excel for Mac 2011 or newer (according to Microsoft's documentation).
    - This formula is also an ARRAY, which may slow things down if you're working with a large dataset.
    - Each time you edit this formula, reconfirm by pressing CTRL+SHIFT+ENTER.

    ,Sheet1!$A:$A)),ROW(Sheet1!B:B)-ROW(Sheet1!B)+1),ROWS(Sheet1!

    NON-ARRAY SOLUTION

    Sheet1
    - Enter table data on Sheet1 as shown here:
    Sheet1 Thumbnail Image

    Sheet2
    - Cell A1 contains the lookup value: Philosophy.
    - Cells A2:C2 contain the header row data: Name Gender Age
    - In cell A3, insert this formula:
    =INDEX(Sheet1!$A2:$D2,MATCH("*"&$A$1&"*",Sheet1!$A2:$A2,0),MATCH(A$2,Sheet1!$A$1:$D$1,0))
    - Press ENTER.
    - Copy across to C3, then down to C6 to get the result shown here:
    Sheet2 Thumbnail Image

    ARRAY SOLUTION

    Sheet1
    - Enter table data on Sheet1 as shown here:
    Sheet1 Thumbnail Image

    Sheet2
    - Cell A1 contains the lookup value: Philosophy.
    - Cells A2:C2 contain the header row data: Name Gender Age
    - In cell A3, insert this formula:
    =IFERROR(INDEX(Sheet1!B$2:B$5,SMALL(IF(ISNUMBER(SEARCH($A$1,Sheet1!$A$2:$A$5)),ROW(Sheet1!B$2:B$5)-ROW(Sheet1!B$2)+1),ROWS(Sheet1!$1:1))),"")
    - Press CTRL+SHIFT+ENTER.
    - Copy across to C3, then down to C6 to get the result shown here:
    Sheet2 Thumbnail Image

    Please Note
    - This formula contains the IFERROR function, which will only work in Excel for PC 2007 or newer. It will also work in Excel for Mac 2011 or newer (according to Microsoft's documentation).
    - This formula is also an ARRAY, which may slow things down if you're working with a large dataset.
    - Each time you edit this formula, reconfirm by pressing CTRL+SHIFT+ENTER.

    :1))),"")
    — Нажмите CTRL+ SHIFT+ ENTER.
    — Скопируйте поперекC3, а затем внизC6, чтобы получить результат, показанный здесь:
    Sheet2 эскиз изображения

    Пожалуйста, Обратите Внимание
    — Эта формула содержит IFERRORфункцию, которая будет работать только в Excel для PC 2007 или новее. Он также будет работать в Excel для Mac 2011 или новее (согласно документации Microsoft).
    — Эта формула также является ARRAYформулой, которая может замедлить работу, если вы работаете с большим набором данных.
    — Каждый раз, когда вы редактируете эту формулу, подтвердите ее нажатием кнопки CTRL+ SHIFT+ ENTER.