Skip i'r prif gynnwys

Vlookup a dychwelyd gwerthoedd lluosog yn seiliedig ar un neu fwy o feini prawf

Fel rheol, gallwch ddefnyddio'r swyddogaeth Vlookup i gael y gwerth cyfatebol cyntaf, ond, weithiau, rydych chi am ddychwelyd yr holl gofnodion paru yn seiliedig ar faen prawf penodol. Yr erthygl hon, byddaf yn siarad am sut i wylio a dychwelyd yr holl werthoedd paru yn fertigol, yn llorweddol neu i mewn i un gell.

Vlookup a dychwelyd yr holl werthoedd cyfatebol yn fertigol

Vlookup a dychwelyd yr holl werthoedd cyfatebol yn llorweddol

Vlookup a dychwelyd yr holl werthoedd cyfatebol i mewn i un gell

Vlookup a dychwelyd yr holl werthoedd cyfatebol yn fertigol

I ddychwelyd yr holl werthoedd paru yn fertigol yn seiliedig ar faen prawf penodol, defnyddiwch y fformiwla arae ganlynol:

1. Rhowch neu ffurfiwch y fformiwla hon i mewn i gell wag lle rydych chi am allbwn y canlyniad:

=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))),"" )

Nodyn: Yn y fformiwla uchod, C2: C20 ydy'r golofn yn cynnwys y cofnod paru rydych chi am ei ddychwelyd; A2: A20 ydy'r golofn yn cynnwys y maen prawf; a E2 yw'r maen prawf penodol yr ydych am ddychwelyd gwerthoedd yn seiliedig arno. Os gwelwch yn dda eu newid i'ch angen.

2. Yna, pwyswch Ctrl + Shift + Enter allweddi gyda'i gilydd i gael y gwerth cyntaf, ac yna llusgwch y ddolen llenwi i lawr i gael yr holl gofnodion cyfatebol yn ôl yr angen, gweler y screenshot:

Awgrym:

I Vlookup a dychwelyd yr holl werthoedd cyfatebol yn seiliedig ar werthoedd mwy penodol yn fertigol, cymhwyswch y fformiwla isod, a gwasgwch Ctrl + Shift + Enter allweddi.

=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 a dychwelyd yr holl werthoedd cyfatebol yn llorweddol

Os ydych chi am i'r gwerthoedd paru gael eu harddangos mewn trefn lorweddol, gall y fformiwla arae isod eich helpu chi.

1. Rhowch neu ffurfiwch y fformiwla hon i mewn i gell wag lle rydych chi am allbwn y canlyniad:

=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))),"")

Nodyn: Yn y fformiwla uchod, C2: C20 ydy'r golofn yn cynnwys y cofnod paru rydych chi am ei ddychwelyd; A2: A20 ydy'r golofn yn cynnwys y maen prawf; a F1 yw'r maen prawf penodol yr ydych am ddychwelyd gwerthoedd yn seiliedig arno. Os gwelwch yn dda eu newid i'ch angen.

2. Yna, pwyswch Ctrl + Shift + Enter allweddi gyda'i gilydd i gael y gwerth cyntaf, ac yna llusgwch y ddolen llenwi yn iawn i gael yr holl gofnodion cyfatebol yn ôl yr angen, gweler y screenshot:

Awgrym:

I Vlookup a dychwelyd yr holl werthoedd cyfatebol yn seiliedig ar werthoedd mwy penodol yn llorweddol, cymhwyswch y fformiwla isod, a gwasgwch Ctrl + Shift + Enter allweddi.

=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 a dychwelyd yr holl werthoedd cyfatebol i mewn i un gell

I wylio a dychwelyd yr holl werthoedd cyfatebol i mewn i un gell, dylech gymhwyso'r fformiwla arae ganlynol.

1. Rhowch neu copïwch y fformiwla isod mewn cell wag:

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

Nodyn: Yn y fformiwla uchod, C2: C20 ydy'r golofn yn cynnwys y cofnod paru rydych chi am ei ddychwelyd; A2: A20 ydy'r golofn yn cynnwys y maen prawf; a F1 yw'r maen prawf penodol yr ydych am ddychwelyd gwerthoedd yn seiliedig arno. Os gwelwch yn dda eu newid i'ch angen.

2. Yna, pwyswch Ctrl + Shift + Enter allweddi gyda'i gilydd i gael yr holl werthoedd paru i mewn i un gell, gweler y screenshot:

Awgrym:

I Vlookup a dychwelyd yr holl werthoedd cyfatebol yn seiliedig ar werthoedd mwy penodol mewn un gell, cymhwyswch y fformiwla isod, a gwasgwch Ctrl + Shift + Enter allweddi.

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

Nodyn: Dim ond yn Excel 2016 a fersiynau diweddarach y cymhwysodd y fformiwla hon yn llwyddiannus. Os nad oes gennych Excel 2016, edrychwch yma i'w gael i lawr.

Erthyglau Vlookup mwy cymharol:

  • Vlookup A Dychwelyd Data Paru Rhwng Dau Werth Yn Excel
  • Yn Excel, gallwn gymhwyso'r swyddogaeth Vlookup arferol i gael y gwerth cyfatebol yn seiliedig ar ddata penodol. Ond, weithiau, rydyn ni am wylio a dychwelyd y gwerth paru rhwng dau werth, sut allech chi ddelio â'r dasg hon yn Excel?
  • Vlookup A Dychwelyd Gwerthoedd Lluosog O'r Rhestr Gostwng
  • Yn Excel, sut allech chi wylio a dychwelyd sawl gwerth cyfatebol o gwymplen, sy'n golygu pan fyddwch chi'n dewis un eitem o'r gwymplen, mae ei holl werthoedd cymharol yn cael eu harddangos ar unwaith fel y dangosir y screenshot canlynol. Yr erthygl hon, byddaf yn cyflwyno'r datrysiad gam wrth gam.
  • Vlookup I Dychwelyd Gwag Yn lle 0 Neu Amherthnasol Yn Excel
  • Fel rheol, pan ddefnyddiwch y swyddogaeth vlookup i ddychwelyd y gwerth cyfatebol, os yw'ch cell sy'n cyfateb yn wag, bydd yn dychwelyd 0, ac os na cheir hyd i'ch gwerth paru, fe gewch wall # Amherthnasol. Yn lle arddangos y gwerth 0 neu # Amherthnasol, sut allwch chi wneud iddo ddangos cell wag?
  • Vlookup I Ddychwelyd Colofnau Lluosog O Dabl Excel
  • Yn nhaflen waith Excel, gallwch gymhwyso'r swyddogaeth Vlookup i ddychwelyd y gwerth paru o un golofn. Ond, weithiau, efallai y bydd angen i chi dynnu gwerthoedd cyfatebol o sawl colofn fel y dangosir y llun a ganlyn. Sut allech chi gael y gwerthoedd cyfatebol ar yr un pryd o sawl colofn trwy ddefnyddio'r swyddogaeth Vlookup?
  • Gwerthoedd Vlookup ar draws taflenni gwaith lluosog
  • Yn rhagori, gallwn gymhwyso'r swyddogaeth vlookup yn hawdd i ddychwelyd y gwerthoedd paru mewn un tabl o daflen waith. Ond, a ydych erioed wedi ystyried sut i edrych ar werth ar draws sawl taflen waith? Gan dybio bod gen i'r tair taflen waith ganlynol gydag ystod o ddata, a nawr, rydw i eisiau cael rhan o'r gwerthoedd cyfatebol yn seiliedig ar y meini prawf o'r tair taflen waith hyn.
  • Bar Fformiwla Gwych (golygu llinellau lluosog o destun a fformiwla yn hawdd); Cynllun Darllen (darllen a golygu nifer fawr o gelloedd yn hawdd); Gludo i'r Ystod Hidlo...
  • Uno Celloedd / Rhesi / Colofnau a Cadw Data; Cynnwys Celloedd Hollt; Cyfuno Rhesi Dyblyg a Swm / Cyfartaledd... Atal Celloedd Dyblyg; Cymharwch y Meysydd...
  • Dewiswch Dyblyg neu Unigryw Rhesi; Dewiswch Blank Rows (mae pob cell yn wag); Darganfyddiad Gwych a Darganfyddiad Niwlog mewn Llawer o Lyfrau Gwaith; Dewis ar Hap ...
  • Copi Union Celloedd Lluosog heb newid cyfeirnod fformiwla; Auto Creu Cyfeiriadau i Daflenni Lluosog; Mewnosod Bwledi, Blychau Gwirio a mwy ...
  • Fformiwlâu Hoff a Mewnosod yn Gyflym, Meysydd, Siartiau a Lluniau; Amgryptio Celloedd gyda chyfrinair; Creu Rhestr Bostio ac anfon e-byst ...
  • Testun Detholiad, Ychwanegu Testun, Tynnu yn ôl Swydd, Tynnwch y Gofod; Creu ac Argraffu Subtotals Paging; Trosi rhwng Cynnwys a Sylwadau Celloedd...
  • Hidlo Super (arbed a chymhwyso cynlluniau hidlo i ddalenni eraill); Trefnu Uwch yn ôl mis / wythnos / dydd, amlder a mwy; Hidlo Arbennig gan feiddgar, italig ...
  • Cyfuno Llyfrau Gwaith a Thaflenni Gwaith; Uno Tablau yn seiliedig ar golofnau allweddol; Rhannwch Ddata yn Daflenni Lluosog; Trosi Swp xls, xlsx a PDF...
  • Grwpio Tabl Pivot yn ôl rhif wythnos, diwrnod o'r wythnos a mwy ... Dangos Celloedd Datgloi, wedi'u Cloi yn ôl gwahanol liwiau; Amlygu Celloedd sydd â Fformiwla / Enw...
tab kte 201905
  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
gwaelod 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