ไปยังเนื้อหาหลัก

Vlookup และส่งคืนค่าหลายค่าตามเกณฑ์หนึ่งหรือหลายเกณฑ์

โดยปกติคุณสามารถใช้ฟังก์ชัน 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 และส่งคืนค่าที่เกี่ยวข้องทั้งหมดลงในเซลล์เดียว

ในการ vlookup และส่งคืนค่าที่เกี่ยวข้องทั้งหมดลงในเซลล์เดียวคุณควรใช้สูตรอาร์เรย์ต่อไปนี้

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 คุณจะ vlookup และส่งคืนค่าที่เกี่ยวข้องหลายค่าจากรายการแบบเลื่อนลงได้อย่างไรซึ่งหมายความว่าเมื่อคุณเลือกหนึ่งรายการจากรายการแบบหล่นลงค่าสัมพัทธ์ทั้งหมดจะแสดงพร้อมกันตามภาพหน้าจอต่อไปนี้ บทความนี้ผมจะแนะนำวิธีการแก้ปัญหาทีละขั้นตอน
  • Vlookup เพื่อคืนค่าว่างแทน 0 หรือ N / A ใน Excel
  • โดยปกติเมื่อคุณใช้ฟังก์ชัน vlookup เพื่อส่งคืนค่าที่สอดคล้องกันหากเซลล์ที่ตรงกันของคุณว่างเปล่าเซลล์จะคืนค่าเป็น 0 และหากไม่พบค่าที่ตรงกันคุณจะได้รับข้อผิดพลาด # ค่า N / A แทนที่จะแสดงค่า 0 หรือ # N / A คุณจะทำให้มันแสดงเซลล์ว่างได้อย่างไร?
  • Vlookup เพื่อส่งคืนหลายคอลัมน์จากตาราง Excel
  • ในแผ่นงาน Excel คุณสามารถใช้ฟังก์ชัน Vlookup เพื่อส่งคืนค่าที่ตรงกันจากคอลัมน์เดียว แต่บางครั้งคุณอาจต้องดึงค่าที่ตรงกันจากหลายคอลัมน์ตามภาพหน้าจอต่อไปนี้ คุณจะได้รับค่าที่สอดคล้องกันในเวลาเดียวกันจากหลายคอลัมน์โดยใช้ฟังก์ชัน Vlookup ได้อย่างไร?
  • ค่า Vlookup ในหลายแผ่นงาน
  • ใน excel เราสามารถใช้ฟังก์ชัน vlookup เพื่อส่งคืนค่าที่ตรงกันในตารางเดียวของแผ่นงาน แต่คุณเคยพิจารณาหรือไม่ว่าจะทำอย่างไรให้ vlookup มีค่าในหลายแผ่นงาน? สมมติว่าฉันมีสามแผ่นงานต่อไปนี้ที่มีช่วงข้อมูลและตอนนี้ฉันต้องการรับส่วนหนึ่งของค่าที่เกี่ยวข้องตามเกณฑ์จากแผ่นงานทั้งสามนี้

เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด

Kutools สำหรับ Excel แก้ปัญหาส่วนใหญ่ของคุณและเพิ่มผลผลิตของคุณได้ถึง 80%

  • ซุปเปอร์ฟอร์มูล่าบาร์ (แก้ไขข้อความและสูตรหลายบรรทัดได้อย่างง่ายดาย); การอ่านเค้าโครง (อ่านและแก้ไขเซลล์จำนวนมากได้อย่างง่ายดาย); วางลงในช่วงที่กรองแล้ว...
  • ผสานเซลล์ / แถว / คอลัมน์ และการเก็บรักษาข้อมูล แยกเนื้อหาของเซลล์ รวมแถวที่ซ้ำกันและผลรวม / ค่าเฉลี่ย... ป้องกันเซลล์ซ้ำ; เปรียบเทียบช่วง...
  • เลือกซ้ำหรือไม่ซ้ำ แถว; เลือกแถวว่าง (เซลล์ทั้งหมดว่างเปล่า); Super Find และ Fuzzy Find ในสมุดงานจำนวนมาก สุ่มเลือก ...
  • สำเนาถูกต้อง หลายเซลล์โดยไม่เปลี่ยนการอ้างอิงสูตร สร้างการอ้างอิงอัตโนมัติ ถึงหลายแผ่น ใส่สัญลักษณ์แสดงหัวข้อย่อย, กล่องกาเครื่องหมายและอื่น ๆ ...
  • แทรกสูตรที่ชื่นชอบและรวดเร็ว, ช่วงแผนภูมิและรูปภาพ; เข้ารหัสเซลล์ ด้วยรหัสผ่าน; สร้างรายชื่อผู้รับจดหมาย และส่งอีเมล ...
  • แยกข้อความ, เพิ่มข้อความ, ลบตามตำแหน่ง, ลบ Space; สร้างและพิมพ์ผลรวมย่อยของเพจ แปลงระหว่างเนื้อหาของเซลล์และความคิดเห็น...
  • ซุปเปอร์ฟิลเตอร์ (บันทึกและใช้โครงร่างตัวกรองกับแผ่นงานอื่น ๆ ); การเรียงลำดับขั้นสูง ตามเดือน / สัปดาห์ / วันความถี่และอื่น ๆ ตัวกรองพิเศษ โดยตัวหนาตัวเอียง ...
  • รวมสมุดงานและแผ่นงาน; ผสานตารางตามคอลัมน์สำคัญ แยกข้อมูลออกเป็นหลายแผ่น; Batch แปลง xls, xlsx และ PDF...
  • การจัดกลุ่มตาราง Pivot ตาม จำนวนสัปดาห์วันในสัปดาห์และอื่น ๆ ... แสดงปลดล็อกเซลล์ที่ถูกล็อก ด้วยสีที่ต่างกัน เน้นเซลล์ที่มีสูตร / ชื่อ...
kte แท็บ 201905
  • เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
  • เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
  • เพิ่มประสิทธิภาพการทำงานของคุณ 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