Прескочи на основното съдържание

Vlookup и връщане на множество стойности въз основа на един или множество критерии

Автор: Xiaoyang Последна промяна: 2023-04-02

Обикновено можете да използвате функцията Vlookup, за да получите първата съответстваща стойност, но понякога искате да върнете всички съответстващи записи въз основа на конкретен критерий. В тази статия ще говоря за това как да направя vlookup и да върна всички съвпадащи стойности вертикално, хоризонтално или в една клетка.

Vlookup и връща всички съответстващи стойности вертикално

Vlookup и връща всички съответстващи стойности хоризонтално

Vlookup и връща всички съответстващи стойности в една клетка

Vlookup и връща всички съответстващи стойности вертикално

За да върнете всички съответстващи стойности вертикално въз основа на конкретен критерий, моля, приложете следната формула за масив:

1. Въведете или копирайте тази формула в празна клетка, където искате да изведете резултата:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($E$2=$A$2:$A$20, ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )

Забележка: В горната формула, C2: C20 дали колоната съдържа съответстващия запис, който искате да върнете; A2: A20 дали колоната съдържа критерия; и E2 е конкретният критерий, въз основа на който искате да върнете стойности. Моля, променете ги според вашите нужди.

2. След това натиснете Ctrl + Shift + Enter ключове заедно, за да получите първата стойност, и след това плъзнете манипулатора за попълване надолу, за да получите всички съответни записи, както ви е необходимо, вижте екранната снимка:

Съвет:

За Vlookup и връщане на всички съответстващи стойности на базата на по-конкретни стойности вертикално, моля, приложете формулата по-долу и натиснете Ctrl + Shift + Enter ключове.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20))*(--($F$2=$B$2:$B$20))), ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )

Vlookup и връща всички съответстващи стойности хоризонтално

Ако искате да получите съответстващите стойности, показани в хоризонтален ред, формулата за масив по-долу може да ви помогне.

1. Въведете или копирайте тази формула в празна клетка, където искате да изведете резултата:

=IFERROR(INDEX($C$2:$C$20,SMALL(IF($F$1=$A$2:$A$20,ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")

Забележка: В горната формула, C2:C20 дали колоната съдържа съответстващия запис, който искате да върнете; A2: A20 дали колоната съдържа критерия; и F1 е конкретният критерий, въз основа на който искате да върнете стойности. Моля, променете ги според вашите нужди.

2. След това натиснете Ctrl + Shift + Enter клавиши заедно, за да получите първата стойност, и след това плъзнете манипулатора за попълване надясно, за да получите всички съответни записи, както ви е необходимо, вижте екранната снимка:

Съвет:

За Vlookup и връщане на всички съответстващи стойности на базата на по-конкретни стойности хоризонтално, моля, приложете формулата по-долу и натиснете Ctrl + Shift + Enter ключове.

=IFERROR(INDEX($C$2:$C$20,SMALL(IF(1=((--($F$1=$A$2:$A$20))*(--($F$2=$B$2:$B$20))),ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")

Vlookup и връща всички съответстващи стойности в една клетка

За vlooking и връщане на всички съответстващи стойности в една клетка, трябва да приложите следната формула за масив.

1. Въведете или копирайте формулата по-долу в празна клетка:

=TEXTJOIN(", ",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

Забележка: В горната формула, C2:C20 дали колоната съдържа съответстващия запис, който искате да върнете; A2: A20 дали колоната съдържа критерия; и F1 е конкретният критерий, въз основа на който искате да върнете стойности. Моля, променете ги според вашите нужди.

2. След това натиснете Ctrl + Shift + Enter ключове заедно, за да получите всички съответстващи стойности в една клетка, вижте екранната снимка:

Съвет:

За Vlookup и връщане на всички съвпадащи стойности въз основа на по-конкретни стойности в една клетка, моля, приложете формулата по-долу и натиснете Ctrl + Shift + Enter ключове.

=TEXTJOIN(", ",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

Забележка: Тази формула се прилага успешно само в Excel 2016 и по-нови версии. Ако нямате Excel 2016, моля, вижте тук за да го сваля.

Още относителни статии за Vlookup:

  • Vlookup и връщане на съвпадащи данни между две стойности в Excel
  • В Excel можем да приложим нормалната функция Vlookup, за да получим съответната стойност въз основа на дадени данни. Но понякога искаме да направим vlookup и да върнем съвпадащата стойност между две стойности, как бихте могли да се справите с тази задача в Excel?
  • Vlookup и връщане на множество стойности от падащия списък
  • В Excel, как бихте могли да направите vlooking и да върнете множество съответни стойности от падащ списък, което означава, че когато изберете един елемент от падащия списък, всичките му относителни стойности се показват наведнъж, както е показано на следната екранна снимка. В тази статия ще представя решението стъпка по стъпка.
  • Vlookup за връщане на празно вместо 0 или N/A в Excel
  • Обикновено, когато приложите функцията vlookup за връщане на съответната стойност, ако съвпадащата ви клетка е празна, тя ще върне 0, а ако съвпадащата ви стойност не бъде намерена, ще получите стойност за грешка #N/A. Вместо да показвате стойността 0 или #N/A, как можете да я накарате да показва празна клетка?
  • Vlookup за връщане на множество колони от таблица на Excel
  • В работния лист на Excel можете да приложите функцията Vlookup, за да върнете съвпадащата стойност от една колона. Но понякога може да се наложи да извлечете съответстващи стойности от множество колони, както е показано на следната екранна снимка. Как бихте могли да получите съответните стойности едновременно от множество колони с помощта на функцията Vlookup?
  • Vlookup стойности в множество работни листове
  • В excel можем лесно да приложим функцията vlookup, за да върнем съответстващите стойности в една таблица на работен лист. Но замисляли ли сте се как да направите vlookup стойност в множество работни листове? Да предположим, че имам следните три работни листа с диапазон от данни и сега искам да получа част от съответните стойности въз основа на критериите от тези три работни листа.
  • Супер Формула Бар (лесно редактиране на няколко реда текст и формула); Оформление за четене (лесно четене и редактиране на голям брой клетки); Поставяне във филтриран диапазон...
  • Обединяване на клетки/редове/колони и съхраняване на данни; Съдържание на разделени клетки; Комбинирайте дублиращи се редове и сума/средно... Предотвратяване на дублиращи се клетки; Сравнете диапазони...
  • Изберете Дублиран или Уникален редове; Изберете Празни редове (всички клетки са празни); Super Find и Fuzzy Find в много работни тетрадки; Произволен избор...
  • Точно копие Множество клетки без промяна на референтната формула; Автоматично създаване на препратки към множество листа; Вмъкване на куршуми, квадратчета за отметка и други...
  • Любими и бързо вмъкнати формули, диапазони, диаграми и снимки; Шифроване на клетки с парола; Създаване на пощенски списък и изпращайте имейли...
  • Извличане на текст, Добавяне на текст, Премахване по позиция, Премахване на пространството; Създаване и отпечатване на междинни суми за пейджинг; Конвертиране на съдържание и коментари между клетки...
  • Супер филтър (запазване и прилагане на филтърни схеми към други листове); Разширено сортиране по месец/седмица/ден, честота и други; Специален филтър с удебелен шрифт, курсив...
  • Комбинирайте работни тетрадки и работни листове; Обединяване на таблици въз основа на ключови колони; Разделете данните на няколко листа; Пакетно конвертиране на xls, xlsx и PDF...
  • Групиране на обобщена таблица по номер на седмицата, ден от седмицата и други... Показване на отключени, заключени клетки с различни цветове; Маркирайте клетки, които имат формула/име...
kte tab 201905
  • Разрешете редактиране и четене с раздели в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Отваряйте и създавайте множество документи в нови раздели на един и същ прозорец, а не в нови прозорци.
  • Увеличава вашата производителност с 50% и намалява стотици кликвания на мишката за вас всеки ден!
officetab отдолу
Comments (8)
No ratings yet. Be the first to rate!
Ahmed Abosamaha
Ahmed Abosamaha
This comment was minimized by the moderator on the site
gents

would you please share with me the formula of the three ways but for data in horizontal table.
Reply
0
0
skyyang
skyyang     Ahmed Abosamaha
This comment was minimized by the moderator on the site
Hello, Ahmed,
To solve your problem, please apply the below formulas:
Get the results vertically: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($A$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), ROW(1:1))),"" )
Get the results horizontally: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($D$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), COLUMN(A1))),"" )
Ge the results in one cell: =TEXTJOIN(", ",TRUE,IF($B$1:$K$1=J5,$B$2:$K$2,""))
Note: These formulas are array formulas, you should press Ctrl + Shift + Enter keys together to get the correct result.

https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-vlookup-data.png

Pease try, hope this can help you!
Reply
Report
0
0
Ahmed Abosamaha
Ahmed Abosamaha     skyyang
This comment was minimized by the moderator on the site
thanks for your greet support and quick response
Reply
0
0
itika
itika
This comment was minimized by the moderator on the site
how do u get all rows to fill up like in yr video without pressing ctrl+shift+enter
Reply
0
0
Taia
Taia
This comment was minimized by the moderator on the site
After I extend the data, some of the cells is showing a "0" instead of blank
Reply
0
0
Adam
Adam     Taia
This comment was minimized by the moderator on the site
It means that there is missing data or the wrong type of data in the cell(s) from the column that is being referrenced. For instance, Im working with a bunch of data and I want all ID numbers for workers under a certain supervisor. However, in the table column being referenced with all workers' ID numbers, some cells had the workers last name in it and not their ID numbers. Those exact cells produced 0s when the entire formula was put in, and this is because it was text within a column that is mostly numbers.
Reply
0
0
Win E
Win E
This comment was minimized by the moderator on the site
How can you tweak the formula so that you have multiple outputs in column D? E.g. I want to populate column D with each of the countries, such that all of their respective cities will be returned in E? The only work around I have can see is manually changing $D$2
Reply
0
0
Jeff
Jeff
This comment was minimized by the moderator on the site
Thank you very much, you save lots of work here!





Regarding your formula {=TEXTJOIN(", ",TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,"")},

I changed a little : (=TEXTJOIN(CHAR(10),TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,""))}





Regards,



Jeff
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations