Перейти до основного матеріалу

Як повернути кілька відповідних значень на основі одного чи кількох критеріїв у Excel?

Зазвичай пошук певного значення та повернення відповідного елемента є простим для більшості з нас за допомогою функції VLOOKUP. Але, ви коли-небудь намагалися повернути кілька відповідних значень на основі одного або декількох критеріїв, як показано на наступному скріншоті? У цій статті я представив кілька формул для вирішення цього складного завдання в Excel.

Повернути декілька значень відповідності на основі одного або декількох критеріїв за допомогою формул масиву


Повернути декілька значень відповідності на основі одного або декількох критеріїв за допомогою формул масиву

Наприклад, я хочу вилучити всі імена, яким 28 років і походять із США, застосуйте таку формулу:

1. Скопіюйте або введіть формулу нижче в порожню комірку, де ви хочете знайти результат:

=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

примітки: У наведеній вище формулі, B2: B11 - стовпець, з якого повертається відповідне значення; F2, C2: C11 є першою умовою та даними стовпця, що містить першу умову; G2, D2: D11 є другою умовою та даними стовпця, що містить цю умову, будь ласка, змініть їх відповідно до ваших потреб.

2. Потім натисніть Ctrl + Shift + Enter ключі, щоб отримати перший результат відповідності, а потім виберіть першу комірку формули і перетягніть маркер заповнення вниз до комірок, поки не відобразиться значення помилки, тепер усі відповідні значення повертаються, як показано на знімку екрана:

Tips : Якщо вам просто потрібно повернути всі відповідні значення на основі однієї умови, застосуйте наведену нижче формулу масиву:

=IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1), ROW(1:1))),"" )


Більше відносних статей:

  • Повернути кілька значень пошуку в одній комірці, розділеній комами
  • В Excel ми можемо застосувати функцію VLOOKUP для повернення першого зіставленого значення з комірок таблиці, але, іноді, нам потрібно витягнути всі відповідні значення, а потім розділити їх певним роздільником, таким як кома, тире тощо, в одне ціле комірка, як показано на наступному скріншоті. Як ми могли отримати та повернути декілька значень пошуку в одній комі, розділеній комами в Excel?
  • Перегляд і повернення декількох відповідних значень одночасно в аркуші Google
  • Звичайна функція Vlookup в аркуші Google може допомогти вам знайти та повернути перше відповідне значення на основі даних. Але, іноді, вам може знадобитися vlookup і повернути всі відповідні значення, як показано на наступному знімку екрана. Чи є у вас якісь хороші та прості способи вирішити це завдання в аркуші Google?
  • Перегляд і повернення декількох значень зі спадного списку
  • Як в Excel можна шукати та повертати кілька відповідних значень зі спадного списку, що означає, що коли ви вибираєте один елемент зі спадного списку, усі його відносні значення відображаються одночасно, як показано на наступному знімку екрана. У цій статті я буду представляти рішення поетапно.
  • Перегляд і повернення декількох значень по вертикалі в Excel
  • Зазвичай ви можете використовувати функцію Vlookup для отримання першого відповідного значення, але, іноді, ви хочете повернути всі відповідні записи на основі певного критерію. У цій статті я розповім про те, як шукати і повертати всі відповідні значення вертикально, горизонтально або в одну клітинку.
  • Перегляд та повернення відповідних даних між двома значеннями в Excel
  • В Excel ми можемо застосувати звичайну функцію Vlookup, щоб отримати відповідне значення на основі даних. Але, іноді, ми хочемо здійснити пошук і повернути значення відповідності між двома значеннями, як показано на наведеному нижче знімку екрана, як би ви могли впоратися з цим завданням у Excel?

 


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці та Ведення даних; Вміст розділених комірок; Поєднуйте повторювані рядки та суму / середнє... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Улюблені та швидко вставлені формули, Діапазони, діаграми та зображення; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Групування зведеної таблиці за номер тижня, день тижня та багато іншого ... Показати розблоковані, заблоковані клітини за різними кольорами; Виділіть клітини, які мають формулу / назву...
вкладка kte 201905
  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці

 

Comments (25)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi, if there are duplicate values (e.g. two adams), how do i make sure that it only returns 1 adam and not 2?
This comment was minimized by the moderator on the site
Hello, Bobby,
To extract only unique matching values, you should apply the below formula:
After pasting the formula, please press Ctrl + Shift + Enter keys together to get the correct result.
=IFERROR(INDEX($B$2:$B$5, MATCH(0, COUNTIF(H1:$H$1, $B$2:$B$5)+IF($D$2:$D$5<>$G$2, 1, 0)+IF($C$2:$C$5<>$F$2, 1, 0), 0)), "")

Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Hi,

Thanks for posting these examples.
I'm trying to implement this in my own sheet, but don't get it to work (maybe because I'm using an europe version of excel)?

I want to get the dates of the days that I had my shifts or that I have worked 'some' (>0) hours for a client.

So in I3 is the name and in J3 the month. K3 and L3 are the shifts (1 is worked) and hours (don't know how to set this, should be more than zero)

My expected results are in:
Shifts: I7 and I8
hours: J7

So I worked more than 0 hours for 'person 2' in oktober on 3-10-2022
had shifts for person 2 on '10-10-2022' and 28-10-2022

When I add '=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))' in my excel sheet, it doesn't allow the comma between the different parts of the formula.
So I need to change them to ';'.
But when I try it it always says: '#NAME?'

So can someone help me with this?

Kind regards,

Bas
[img]https://drive.google.com/file/d/1iIPQKuj_PNhqWyWlwJ4IQTqGNEd6B9Hw/view?usp=share_link[/img]
This comment was minimized by the moderator on the site
Hi,

First, thank you for sharing!

Can you please provide a solution to the case below:

I have 3 columns (A: Containing reference information, B: Containing information to be searched, C: Search result)

Image url is provided below

https://ibb.co/VHCd09K

Column A-------------------------Column B------------Column C
File Name-------------------------Name----------------File Name, Document Name, Element Name, Name
Changed Element-----------------Element--------------Changed Element, Element Name, Element ID
Column Location
Document Name
Element Name
Name
Category
Warranty
Slope
Element ID

What I need is to search in column A for any partial match with cell B2 (Name) or B3 (Element) and get the result in one cell,

Thank you, Behzad
This comment was minimized by the moderator on the site
Hello, Behzad
Maybe the below User Defined Function can help you.
Public Function ConcatPartLookUp(rngInput As Range, rngSource As Range, Optional strDelimiter As String, Optional blCaseSensitive)
Dim rng As Range
If strDelimiter = "" Then strDelimiter = ","
If IsMissing(blCaseSensitive) Then
    blCaseSensitive = False
Else
    blCaseSensitive = True
End If
For Each rng In rngSource
    If blCaseSensitive Then
        If InStr(1, rng.Value, rngInput.Value, vbBinaryCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    Else
        If InStr(1, rng.Value, rngInput.Value, vbTextCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    End If
Next
If Len(ConcatPartLookUp) > 0 Then ConcatPartLookUp = Mid(ConcatPartLookUp, 2, Len(ConcatPartLookUp))
End Function


After copying and pasting this code, and then use this formula:=ConcatPartLookUp(B2,$A$2:$A$8) to get the result you need.
Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Heyi, thank you for the formula. It worked for "fixed" values / text as criterias. However, one of the criterias i'm trying to use is a condition (values <>0 ), but does not work the described formula. Do you guys know what should i change to adapt the formula so I can have a condition as one of the criterias, please?

Best,

João
This comment was minimized by the moderator on the site
Hello, Marcus
To solve your problem, please view this article:
https://www.extendoffice.com/documents/excel/6393-excel-vlookup-function.html#b3-2
There are some detailed explanations of this task. You just need to change the criteira to your own.
Thank you!
This comment was minimized by the moderator on the site
Hi,

if in cell H1 i write "Name" and wanted to link that with the formula, how would that work?
Then I could write "ID" in cell H1 and would automatically get as a result: AA1004; DD1009; PP1023 (for the first formula)

Thank you in advance!
This comment was minimized by the moderator on the site
Hello, Marie
Sorry, i can't get the point of your first problem, could you explain your problem more clearly and detailedly? Or you can insert a screenshot here to describe your problem.
As for the second question, you just need to change the cell reference as this:
=INDEX($A$2:$A$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Remeber to press Ctrl + Shift + Enter keys together.
Please try, hope it can help you!
This comment was minimized by the moderator on the site
Hello,
what would be needed to expand the first formula in the following case:
Some IDs are Blank (e.g. cell A5 is blank) and I would like an additional condition outputting lines only when the IDs are not blank. (So the output should then be James and Abdul.
Thanks!
This comment was minimized by the moderator on the site
Hello, Jo,
To solve your problem, please apply the below formula:
=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11)*($A$2:$A$11<>0), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Please have a ry, hope it can help you!
This comment was minimized by the moderator on the site
hello,
ive tried using the formula and it either generates a value of 0 or the image attached
This comment was minimized by the moderator on the site
Hello, Milku
Your screenshot showed WPS software of MAC version, so I am not sure whether our formula is available.
I uploaded an Excel file to here, you can try to see if it can calculate correctly in you environment.
Thank you!
This comment was minimized by the moderator on the site
When I use the second formula and drag down, nothing appears. The formula result (fx) says it should be returning something but it is blank. How do I correct this?
This comment was minimized by the moderator on the site
Hello Alysia,

Glad to help. I tried the second formula in the article and drag the formula down, the rest of results were returned. I think there may be two reasons for your problem. First, maybe you forget to press Ctrl + Shift + Enter keys to enter the formula. Second, the matching result is only one, so no other results are not returned. Please have a check.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
Hi,

I was wondering if it at all possible to enter a 2nd criteria but from the same range as the 1st criteria,

For example with the used example above i would like to search for the names of people from both America and France So cell F3 would have France, Scarlett & Andrew would also populate in the list in Column G

Thank you for assistance in advance.
This comment was minimized by the moderator on the site
Hello Nick,

Glad to help. If you want to get the names of people from both America and France, I advise you to use our formula twice to get the result. Please see the screenshot, In F2 and G2 are values "United States" and "France". Apply formula =IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1), ROW(1:1))),"" ) to get the results for America. And apply formula =IFERROR(INDEX($B$2:$B$11, SMALL(IF($G$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1), ROW(1:1))),"" ) to get the results for France. It's simple. Please have a try.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
In the first example, what change to the formula would be needed to return everyone who was less than 28 years old?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations