20+ ตัวอย่าง VLOOKUP สำหรับผู้ใช้ Excel ระดับเริ่มต้นและระดับสูง

ฟังก์ชัน VLOOKUP เป็นหนึ่งในฟังก์ชันยอดนิยมใน Excel บทช่วยสอนนี้จะแนะนำวิธีใช้ฟังก์ชัน VLOOKUP ใน Excel พร้อมตัวอย่างพื้นฐานและขั้นสูงมากมายทีละขั้นตอน

บทนำของฟังก์ชัน VLOOKUP - ไวยากรณ์และอาร์กิวเมนต์

ใน Excel ฟังก์ชัน VLOOKUP เป็นฟังก์ชันที่มีประสิทธิภาพสำหรับผู้ใช้ Excel ส่วนใหญ่ ช่วยให้คุณค้นหาค่าทางซ้ายสุดของช่วงข้อมูล และส่งกลับค่าที่ตรงกันในแถวเดียวกันจากคอลัมน์ที่คุณระบุตามภาพหน้าจอต่อไปนี้ .

ไวยากรณ์ของฟังก์ชัน VLOOKUP:

ข้อโต้แย้ง:

หมายเหตุฟังก์ชัน:

ตัวอย่าง VLOOKUP พื้นฐาน

ในส่วนนี้ เราจะพูดถึงสูตร Vlookup ที่คุณใช้บ่อยๆ

2.1 การจับคู่แบบตรงทั้งหมดและการจับคู่โดยประมาณ VLOOKUP

 2.1.1 ทำการจับคู่แบบตรงทั้งหมด VLOOKUP

โดยปกติ หากคุณกำลังมองหาการจับคู่แบบตรงทั้งหมดกับฟังก์ชัน VLOOKUP คุณเพียงแค่ต้องใช้ FALSE เป็นอาร์กิวเมนต์สุดท้าย

ตัวอย่างเช่นหากต้องการรับคะแนนคณิตศาสตร์ที่สอดคล้องกันโดยพิจารณาจากหมายเลขประจำตัวที่ระบุโปรดทำดังนี้:

โปรดคัดลอกและวางสูตรด้านล่างนี้ลงในเซลล์ว่าง (ในที่นี้ ฉันเลือก G2) และกดปุ่ม "Enter" เพื่อรับผลลัพธ์:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

สูตร VLOOKUP ทำงานอย่างไร

ขั้นแรก ค้นหา ID - C1005 ในคอลัมน์ซ้ายสุดของตาราง ไล่จากบนลงล่างแล้วหาค่าในเซลล์ A6

เมื่อพบค่าแล้วจะเลี้ยวขวาไปที่คอลัมน์ที่สามและแยกค่าในนั้นออกมา

ดังนั้นคุณจะได้ผลลัพธ์ตามภาพหน้าจอด้านล่าง:

 2.1.2 ทำการจับคู่ VLOOKUP โดยประมาณ

การจับคู่โดยประมาณมีประโยชน์สำหรับการค้นหาค่าระหว่างช่วงข้อมูล หากไม่พบค่าที่ตรงกันทุกประการ VLOOKUP โดยประมาณจะส่งกลับค่าที่มากที่สุดซึ่งน้อยกว่าค่าที่ค้นหา

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

ขั้นตอนที่ 1: ใช้สูตร VLOOKUP และเติมลงในเซลล์อื่น

คัดลอกและวางสูตรต่อไปนี้ลงในเซลล์ที่คุณต้องการใส่ผลลัพธ์ จากนั้นลากจุดจับเติมลงเพื่อใช้สูตรนี้กับเซลล์อื่น

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

ผลลัพธ์:

ตอนนี้ คุณจะได้รับการจับคู่โดยประมาณตามค่าที่กำหนด ดูภาพหน้าจอ:

2.2 ทำ VLOOKUP ที่คำนึงถึงตัวพิมพ์เล็กและใหญ่ใน Excel

ตามค่าเริ่มต้น ฟังก์ชัน VLOOKUP จะทำการค้นหาโดยไม่คำนึงถึงตัวพิมพ์เล็กและใหญ่ ซึ่งหมายความว่าจะถือว่าตัวพิมพ์เล็กและตัวพิมพ์ใหญ่เหมือนกัน ในบางครั้ง คุณอาจต้องทำการค้นหาโดยคำนึงถึงตัวพิมพ์เล็กและใหญ่ใน Excel ฟังก์ชัน VLOOKUP ปกติอาจไม่สามารถแก้ปัญหานี้ได้ ในกรณีนี้ คุณสามารถใช้ฟังก์ชันทางเลือก เช่น INDEX และ MATCH กับฟังก์ชัน EXACT หรือฟังก์ชัน LOOKUP และ EXACT

ตัวอย่างเช่นฉันมีช่วงข้อมูลต่อไปนี้ซึ่งคอลัมน์ ID มีสตริงข้อความที่มีตัวพิมพ์ใหญ่หรือตัวพิมพ์เล็กตอนนี้ฉันต้องการส่งคืนคะแนนคณิตศาสตร์ที่สอดคล้องกันของหมายเลข ID ที่ระบุ

ขั้นตอนที่ 1: ใช้สูตรใดสูตรหนึ่งแล้วเติมลงในเซลล์อื่น

โปรดคัดลอกและวางหนึ่งในสูตรด้านล่างลงในเซลล์ว่างที่คุณต้องการรับผลลัพธ์ จากนั้นเลือกเซลล์สูตร ลากที่จับเติมลงไปยังเซลล์ที่คุณต้องการเติมสูตรนี้

สูตร 1: หลังจากวางสูตรแล้วโปรดกดปุ่ม "Ctrl" + "Shift" + "Enter"

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

สูตรที่ 2 : หลังจากวางสูตรแล้วกรุณากดปุ่ม "Enter"

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

ผลลัพธ์:

จากนั้นคุณจะได้รับผลลัพธ์ที่ถูกต้องตามที่คุณต้องการ ดูภาพหน้าจอ:

2.3 ค่า VLOOKUP จากขวาไปซ้ายใน Excel

ฟังก์ชัน VLOOKUP จะค้นหาค่าในคอลัมน์ซ้ายสุดของช่วงข้อมูลเสมอ และส่งกลับค่าที่สอดคล้องกันจากคอลัมน์ทางขวา หากคุณต้องการดำเนินการย้อนกลับ VLOOKUP ซึ่งหมายถึงการค้นหาค่าเฉพาะในคอลัมน์ด้านขวาและส่งคืนค่าที่สอดคล้องกันในคอลัมน์ด้านซ้ายตามภาพด้านล่างที่แสดง:

คลิกเพื่อทราบรายละเอียดทีละขั้นตอนเกี่ยวกับงานนี้ ...

2.4 VLOOKUP ค่าที่สอง ที่ n หรือสุดท้ายที่ตรงกันใน Excel

โดยปกติ หากพบค่าที่ตรงกันหลายค่าเมื่อใช้ฟังก์ชัน Vlookup ระบบจะส่งกลับเฉพาะระเบียนแรกที่ตรงกันเท่านั้น ในส่วนนี้ ฉันจะพูดถึงวิธีรับค่าที่ตรงกันที่สอง ที่ n หรือสุดท้ายในช่วงข้อมูล

 2.4.1 VLOOKUP และส่งคืนค่าที่ตรงกันลำดับที่ 2 หรือ n

สมมติว่าคุณมีรายชื่อในคอลัมน์ A ซึ่งเป็นหลักสูตรฝึกอบรมที่พวกเขาซื้อในคอลัมน์ B ตอนนี้ คุณกำลังค้นหาหลักสูตรฝึกอบรมที่ 2 หรือ n ที่ลูกค้ารายดังกล่าวซื้อ ดูภาพหน้าจอ:

ที่นี่ ฟังก์ชัน VLOOKUP อาจไม่สามารถแก้ปัญหานี้ได้โดยตรง แต่คุณสามารถใช้ฟังก์ชัน INDEX เป็นทางเลือกได้

ขั้นตอนที่ 1: ใช้และเติมสูตรลงในเซลล์อื่น

ตัวอย่างเช่น หากต้องการรับค่าที่ตรงกันครั้งที่สองตามเกณฑ์ที่กำหนด โปรดใช้สูตรต่อไปนี้ในเซลล์ว่าง แล้วกดแป้น "Ctrl" + "Shift" + "Enter" พร้อมกันเพื่อรับผลลัพธ์แรก จากนั้นเลือกเซลล์สูตร ลากจุดจับเติมลงไปที่เซลล์ที่คุณต้องการเติมสูตรนี้

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

ผลลัพธ์:

ขณะนี้ ค่าที่ตรงกันที่สองทั้งหมดตามชื่อที่ระบุได้แสดงพร้อมกัน

 2.4.2 VLOOKUP และส่งคืนค่าที่ตรงกันล่าสุด

หากคุณต้องการ vlookup และส่งคืนค่าที่ตรงกันล่าสุดตามภาพด้านล่างที่แสดงสิ่งนี้ VLOOKUP และส่งคืนค่าที่ตรงกันล่าสุด บทช่วยสอนอาจช่วยให้คุณได้รับรายละเอียดค่าการจับคู่ล่าสุด

2.5 VLOOKUP จับคู่ค่าระหว่างสองค่าหรือวันที่ที่กำหนด

บางครั้ง คุณอาจต้องการค้นหาค่าระหว่างสองค่าหรือวันที่ และส่งคืนผลลัพธ์ที่สอดคล้องกันดังที่แสดงในภาพหน้าจอด้านล่าง ในกรณีเช่นนี้ คุณสามารถใช้ฟังก์ชัน LOOKUP แทนฟังก์ชัน VLOOKUP กับตารางที่เรียงลำดับได้

 2.5.1 VLOOKUP จับคู่ค่าระหว่างสองค่าที่กำหนดหรือวันที่ด้วยสูตร

ขั้นตอนที่ 1: จัดเรียงข้อมูลและใช้สูตรต่อไปนี้

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

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

ผลลัพธ์:

และตอนนี้ คุณจะได้รับบันทึกที่ตรงกันทั้งหมดตามค่าที่กำหนด ดูภาพหน้าจอ:

 2.5.2 การจับคู่ค่า VLOOKUP ระหว่างค่าหรือวันที่ที่กำหนดสองค่าด้วยฟีเจอร์ที่มีประโยชน์

หากคุณพบว่าการจำและเข้าใจสูตรข้างต้นเป็นเรื่องยาก ที่นี่ฉันจะแนะนำเครื่องมือที่ใช้งานง่าย - "Kutools for Excel" พร้อมด้วยฟีเจอร์ "LOOKUP ระหว่างค่าสองค่า" ช่วยให้คุณส่งคืนรายการที่สอดคล้องกันโดยอิงตามค่าหรือวันที่ที่ระบุระหว่างค่าหรือวันที่สองค่าได้อย่างง่ายดาย

1. คลิก “Kutools” > “Super LOOKUP” > “LOOKUP ระหว่างค่าสองค่า” เพื่อเปิดใช้งานฟีเจอร์นี้
2. จากนั้นระบุการดำเนินการจากกล่องโต้ตอบตามข้อมูลของคุณ

2.6 การใช้สัญลักษณ์แทนสำหรับการจับคู่บางส่วนในฟังก์ชัน VLOOKUP

ใน Excel สามารถใช้สัญลักษณ์แทนภายในฟังก์ชัน VLOOKUP ซึ่งช่วยให้คุณสามารถจับคู่ค่าการค้นหาบางส่วนได้ ตัวอย่างเช่น คุณสามารถใช้ VLOOKUP เพื่อส่งกลับค่าที่ตรงกันจากตารางตามส่วนของค่าการค้นหา

สมมติว่าฉันมีช่วงข้อมูลตามภาพด้านล่างที่แสดงตอนนี้ฉันต้องการแยกคะแนนตามชื่อ (ไม่ใช่ชื่อเต็ม) จะแก้ปัญหานี้ใน Excel ได้อย่างไร

ขั้นตอนที่ 1: นำสูตรไปประยุกต์ใช้และเติมลงในเซลล์อื่น

โปรดคัดลอกหรือป้อนสูตรต่อไปนี้ลงในเซลล์ว่าง จากนั้นลากจุดจับเติมเพื่อเติมสูตรนี้ลงในเซลล์อื่นที่คุณต้องการ:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

ผลลัพธ์:

และคะแนนที่ตรงกันทั้งหมดถูกส่งคืนตามภาพด้านล่างที่แสดง:

2.7 ค่า VLOOKUP จากแผ่นงานอื่น

โดยปกติแล้ว คุณอาจต้องทำงานกับเวิร์กชีตมากกว่าหนึ่งแผ่น ฟังก์ชัน VLOOKUP สามารถใช้เพื่อค้นหาข้อมูลจากชีตอื่นได้เหมือนกับในเวิร์กชีตหนึ่ง

ตัวอย่างเช่นคุณมีแผ่นงานสองแผ่นตามภาพหน้าจอด้านล่างเพื่อค้นหาและส่งคืนข้อมูลที่เกี่ยวข้องจากแผ่นงานที่คุณระบุโปรดทำตามขั้นตอนต่อไปนี้:

ขั้นตอนที่ 1: นำสูตรไปประยุกต์ใช้และเติมลงในเซลล์อื่น

โปรดป้อนหรือคัดลอกสูตรด้านล่างลงในเซลล์ว่างที่คุณต้องการรับรายการที่ตรงกัน จากนั้นลากที่จับเติมลงไปที่เซลล์ที่คุณต้องการใช้สูตรนี้

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

ผลลัพธ์:

คุณจะได้ผลลัพธ์ที่สอดคล้องตามที่คุณต้องการ ดูภาพหน้าจอ:

2.8 ค่า VLOOKUP จากสมุดงานอื่น

ส่วนนี้จะพูดถึงการค้นหาและส่งคืนค่าที่ตรงกันจากสมุดงานอื่นโดยใช้ฟังก์ชัน VLOOKUP

ตัวอย่างเช่น สมมติว่าคุณมีสมุดงานสองเล่ม เวิร์กบุ๊กแรกประกอบด้วยรายการผลิตภัณฑ์และต้นทุนที่เกี่ยวข้อง ในสมุดงานที่สอง คุณต้องการแยกต้นทุนที่สอดคล้องกันสำหรับแต่ละรายการผลิตภัณฑ์ตามภาพด้านล่างที่แสดง

ขั้นตอนที่ 1: นำสูตรไปใช้

เปิดทั้งสมุดงานที่คุณต้องการใช้ จากนั้นใช้สูตรต่อไปนี้ในเซลล์ที่คุณต้องการใส่ผลลัพธ์ในสมุดงานที่สอง จากนั้นลากและคัดลอกสูตรนี้ไปยังเซลล์อื่นที่คุณต้องการ

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

ผลลัพธ์:

2.9 ส่งคืนข้อความว่างเปล่าหรือเฉพาะเจาะจงแทน 0 หรือข้อผิดพลาด #N/A

โดยปกติ เมื่อคุณใช้ฟังก์ชัน VLOOKUP เพื่อส่งกลับค่าที่สอดคล้องกัน หากเซลล์ที่ตรงกันว่างเปล่า เซลล์ที่ตรงกันจะส่งกลับค่าเป็น 0 และหากไม่พบค่าที่ตรงกัน คุณจะได้รับค่าความผิดพลาด #N/A ดังที่แสดงในส่วน ภาพหน้าจอด้านล่าง หากคุณต้องการแสดงเซลล์ว่างหรือค่าเฉพาะแทน 0 หรือ #N/A ให้ทำเช่นนี้ VLOOKUP เพื่อส่งคืนค่าว่างหรือค่าเฉพาะ แทนที่จะเป็น 0 หรือ N/A บทช่วยสอนอาจช่วยคุณได้

ตัวอย่าง VLOOKUP ขั้นสูง

3.1 การค้นหาแบบสองทาง (VLOOKUP ในแถวและคอลัมน์)

บางครั้ง คุณอาจต้องทำการค้นหาแบบ 2 มิติ ซึ่งหมายถึงการค้นหาค่าในแถวและคอลัมน์พร้อมกัน ตัวอย่างเช่น หากคุณมีช่วงข้อมูลต่อไปนี้ และคุณอาจต้องรับค่าสำหรับผลิตภัณฑ์เฉพาะในไตรมาสที่ระบุ ส่วนนี้จะแนะนำสูตรสำหรับจัดการกับงานนี้ใน Excel

ใน Excel คุณสามารถใช้ฟังก์ชัน VLOOKUP และ MATCH ร่วมกันเพื่อค้นหาแบบสองทางได้

โปรดใช้สูตรต่อไปนี้ในเซลล์ว่าง จากนั้นกดปุ่ม "Enter" เพื่อรับผลลัพธ์

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

3.2 ค่าการจับคู่ VLOOKUP ขึ้นอยู่กับเกณฑ์สองเกณฑ์ขึ้นไป

เป็นเรื่องง่ายสำหรับคุณที่จะค้นหาค่าที่ตรงกันตามเกณฑ์หนึ่งเกณฑ์ แต่ถ้าคุณมีเกณฑ์ตั้งแต่สองเกณฑ์ขึ้นไป คุณจะทำอย่างไร

 3.2.1 ค่าการจับคู่ VLOOKUP ขึ้นอยู่กับเกณฑ์สองเกณฑ์ขึ้นไปพร้อมสูตร

ในกรณีนี้ ฟังก์ชัน LOOKUP หรือ MATCH และ INDEX ใน Excel สามารถช่วยคุณแก้ปัญหานี้ได้อย่างรวดเร็วและง่ายดาย

ตัวอย่างเช่นฉันมีตารางข้อมูลด้านล่างหากต้องการส่งคืนราคาที่ตรงกันตามผลิตภัณฑ์และขนาดที่ระบุสูตรต่อไปนี้อาจช่วยคุณได้

ขั้นตอนที่ 1: ใช้สูตรใด ๆ ด้านล่างนี้

สูตรที่ 1: ป้อนสูตรต่อไปนี้และกด "Enter"

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

สูตรที่ 2: ป้อนสูตรต่อไปนี้และกด "Ctrl" + "Shift" + "Enter"

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

ผลลัพธ์:

 3.2.2 การจับคู่ค่า VLOOKUP ตามเกณฑ์สองเกณฑ์ขึ้นไปด้วย Kutools สำหรับ Excel

การจำสูตรที่ซับซ้อนข้างต้นซึ่งต้องใช้ซ้ำหลายครั้งอาจเป็นเรื่องยาก ซึ่งอาจทำให้คุณทำงานช้าลงได้ อย่างไรก็ตาม "Kutools for Excel" นำเสนอฟีเจอร์ "การค้นหาหลายเงื่อนไข" ที่ช่วยให้คุณส่งคืนผลลัพธ์ที่สอดคล้องกันโดยอิงตามเงื่อนไขหนึ่งหรือมากกว่านั้นด้วยการคลิกเพียงไม่กี่ครั้ง

1. คลิก “Kutools” > “Super LOOKUP” > “Multi-condition Lookup” เพื่อเปิดใช้งานฟีเจอร์นี้
2. จากนั้นระบุการดำเนินการจากกล่องโต้ตอบตามข้อมูลของคุณ

3.3 VLOOKUP เพื่อส่งกลับค่าหลายค่าด้วยเกณฑ์อย่างน้อยหนึ่งเกณฑ์

ใน Excel ฟังก์ชัน VLOOKUP จะค้นหาค่าและส่งคืนค่าที่ตรงกันค่าแรกเท่านั้น หากพบค่าที่ตรงกันหลายค่า บางครั้ง คุณอาจต้องการส่งกลับค่าที่เกี่ยวข้องทั้งหมดในแถว ในคอลัมน์ หรือในเซลล์เดียว ส่วนนี้จะพูดถึงวิธีการส่งคืนค่าที่ตรงกันหลายค่าโดยมีเงื่อนไขตั้งแต่หนึ่งเงื่อนไขขึ้นไปในสมุดงาน

 3.3.1 VLOOKUP ค่าที่ตรงกันทั้งหมดตามเงื่อนไขอย่างน้อยหนึ่งเงื่อนไขในแนวนอน

สมมติว่าคุณมีตารางข้อมูลที่ประกอบด้วยประเทศ เมือง และชื่อในช่วง A1:C14 และตอนนี้ คุณต้องการส่งคืนชื่อทั้งหมดในแนวนอนซึ่งมาจาก "US" ดังภาพด้านล่างที่แสดง เพื่อแก้ปัญหานี้โปรด คลิกที่นี่เพื่อรับผลลัพธ์ทีละขั้นตอน.

 3.3.2 VLOOKUP ค่าที่ตรงกันทั้งหมดตามเงื่อนไขตั้งแต่หนึ่งเงื่อนไขขึ้นไปในแนวตั้ง

หากคุณต้องการ Vlookup และส่งคืนค่าที่ตรงกันทั้งหมดในแนวตั้งตามเกณฑ์ที่ระบุดังภาพหน้าจอด้านล่าง โปรดคลิกที่นี่เพื่อรับวิธีแก้ปัญหาโดยละเอียด.

 3.3.3 VLOOKUP ค่าที่ตรงกันทั้งหมดตามเงื่อนไขตั้งแต่หนึ่งข้อขึ้นไปลงในเซลล์เดียว

หากคุณต้องการ Vlookup และส่งคืนค่าที่ตรงกันหลายค่าเป็นเซลล์เดียวพร้อมตัวคั่นที่ระบุ ฟังก์ชันใหม่ของ TEXTJOIN สามารถช่วยคุณแก้ปัญหานี้ได้อย่างรวดเร็วและง่ายดาย.

3.4 VLOOKUP เพื่อส่งกลับทั้งแถวของเซลล์ที่ตรงกัน

ในส่วนนี้ ฉันจะพูดถึงวิธีดึงข้อมูลทั้งแถวของค่าที่ตรงกันโดยใช้ฟังก์ชัน VLOOKUP

ขั้นตอนที่ 1: ใช้สูตรต่อไปนี้

โปรดคัดลอกหรือพิมพ์สูตรด้านล่างลงในเซลล์ว่างที่คุณต้องการแสดงผลลัพธ์ จากนั้นกดปุ่ม "Enter" เพื่อรับค่าแรก จากนั้นลากเซลล์สูตรไปทางขวาจนกว่าจะแสดงข้อมูลของแถวทั้งหมด

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

ผลลัพธ์:

ตอนนี้ คุณสามารถดูข้อมูลแถวทั้งหมดถูกส่งกลับ ดูภาพหน้าจอ:

3.5 VLOOKUP ที่ซ้อนกันใน Excel

บางครั้ง คุณอาจต้องค้นหาค่าที่เชื่อมโยงระหว่างหลายตาราง ในกรณีนี้ คุณสามารถซ้อนฟังก์ชัน VLOOKUP หลายฟังก์ชันเข้าด้วยกันเพื่อรับค่าสุดท้าย

ตัวอย่างเช่น ฉันมีเวิร์กชีตที่มีสองตารางแยกกัน ตารางแรกแสดงชื่อผลิตภัณฑ์ทั้งหมดพร้อมกับพนักงานขายที่เกี่ยวข้อง ตารางที่สองแสดงยอดขายรวมของพนักงานขายแต่ละคน ตอนนี้ ถ้าคุณต้องการค้นหายอดขายของแต่ละผลิตภัณฑ์ ดังที่แสดงในภาพหน้าจอต่อไปนี้ คุณสามารถซ้อนฟังก์ชัน VLOOKUP เพื่อทำงานนี้ให้สำเร็จ

สูตรทั่วไปสำหรับฟังก์ชัน VLOOKUP ที่ซ้อนกันคือ:

ขั้นตอนที่ 1: ใช้และกรอกสูตรต่อไปนี้

โปรดใช้สูตรต่อไปนี้ในเซลล์ว่าง จากนั้นลากที่จับเติมลงไปยังเซลล์ที่คุณต้องการใช้สูตรนี้

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

ผลลัพธ์:

ตอนนี้ คุณจะได้ผลลัพธ์ดังที่แสดงในภาพหน้าจอต่อไปนี้:

3.6 ตรวจสอบว่ามีค่าตามรายการข้อมูลในคอลัมน์อื่นหรือไม่

ฟังก์ชัน VLOOKUP ยังสามารถช่วยคุณตรวจสอบว่ามีค่าตามรายการข้อมูลในคอลัมน์อื่นหรือไม่ ตัวอย่างเช่น หากคุณต้องการค้นหาชื่อในคอลัมน์ C และเพียงแค่ส่งกลับ Yes หรือ No หากพบชื่อนั้นหรือไม่อยู่ในคอลัมน์ A ดังภาพด้านล่างที่แสดง

ขั้นตอนที่ 1: ใช้สูตรต่อไปนี้

โปรดใช้สูตรต่อไปนี้ในเซลล์ว่าง จากนั้นลากที่จับเติมลงไปยังเซลล์ที่คุณต้องการเติมสูตรนี้

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

ผลลัพธ์:

และคุณจะได้ผลลัพธ์ตามที่คุณต้องการ ดูภาพหน้าจอ:

3.7 VLOOKUP และรวมค่าที่ตรงกันทั้งหมดในแถวหรือคอลัมน์

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

 3.7.1 VLOOKUP และรวมค่าที่ตรงกันทั้งหมดในแถวหรือหลายแถว

สมมติว่าคุณมีรายการสินค้าที่มียอดขายหลายเดือน ดังที่แสดงในภาพหน้าจอต่อไปนี้ ตอนนี้ คุณต้องรวมคำสั่งซื้อทั้งหมดในทุกเดือนตามผลิตภัณฑ์ที่กำหนด

ขั้นตอนที่ 1: ใช้สูตรต่อไปนี้

โปรดคัดลอกหรือป้อนสูตรต่อไปนี้ลงในเซลล์ว่าง จากนั้นกดแป้น "Ctrl" + "Shift" + "Enter" พร้อมกันเพื่อรับผลลัพธ์แรก จากนั้นลากจุดจับเติมลงมาเพื่อคัดลอกสูตรนี้ไปยังเซลล์อื่นๆ ที่คุณต้องการ

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

ผลลัพธ์:

ค่าทั้งหมดในแถวของค่าที่ตรงกันค่าแรกได้รับการรวมเข้าด้วยกัน ดูภาพหน้าจอ:

 3.7.2 VLOOKUP และรวมค่าที่ตรงกันทั้งหมดในคอลัมน์หรือหลายคอลัมน์

หากคุณต้องการรวมมูลค่ารวมสำหรับเดือนใดเดือนหนึ่งดังที่แสดงในภาพหน้าจอด้านล่าง ฟังก์ชัน VLOOKUP ปกติอาจช่วยคุณไม่ได้ ในที่นี้ คุณควรใช้ฟังก์ชัน SUM, INDEX และ MATCH ร่วมกันเพื่อสร้างสูตร

ขั้นตอนที่ 1: ใช้สูตรต่อไปนี้

ใช้สูตรด้านล่างลงในเซลล์ว่าง จากนั้นลากที่จับเติมลงเพื่อคัดลอกสูตรนี้ไปยังเซลล์อื่น

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

ผลลัพธ์:

ขณะนี้ ค่าแรกที่ตรงกันตามเดือนที่ระบุในคอลัมน์ได้รับการรวมเข้าด้วยกันแล้ว ดูภาพหน้าจอ:

 3.7.3 VLOOKUP และหาผลรวมของค่าที่ตรงกันค่าแรกหรือค่าที่ตรงกันทั้งหมดด้วย Kutools สำหรับ Excel

บางทีสูตรที่กล่าวข้างต้นอาจจำยากสำหรับคุณ ในกรณีนี้ ฉันขอแนะนำฟีเจอร์อันทรงพลังอย่าง "การค้นหาและผลรวม" ของ "Kutools for Excel" โดยฟีเจอร์นี้จะช่วยให้คุณค้นหาและหาผลรวมของค่าแรกที่ตรงกันหรือค่าทั้งหมดที่ตรงกันในแถวหรือคอลัมน์ได้ง่ายที่สุด

1. คลิก “Kutools” > “Super LOOKUP” > “LOOKUP and Sum” เพื่อเปิดใช้งานฟีเจอร์นี้
2. จากนั้นระบุการดำเนินการจากกล่องโต้ตอบตามความต้องการของคุณ

 3.7.4 VLOOKUP และรวมค่าที่ตรงกันทั้งหมดทั้งในแถวและคอลัมน์

หากคุณต้องการรวมค่าเมื่อคุณต้องการจับคู่ทั้งคอลัมน์และแถวตัวอย่างเช่นเพื่อให้ได้มูลค่ารวมของผลิตภัณฑ์ Sweater ในเดือนมีนาคมตามภาพด้านล่างที่แสดง

ที่นี่ คุณสามารถใช้ฟังก์ชัน SUMPRODCT เพื่อทำงานนี้ให้สำเร็จ

โปรดใช้สูตรต่อไปนี้ในเซลล์ จากนั้นกดปุ่ม "Enter" เพื่อรับผลลัพธ์ ดูภาพหน้าจอ:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

3.8 VLOOKUP เพื่อผสานสองตารางตามคอลัมน์หลัก

ในการทำงานประจำวันของคุณ เมื่อวิเคราะห์ข้อมูล คุณอาจต้องรวบรวมข้อมูลที่จำเป็นทั้งหมดไว้ในตารางเดียวตามคอลัมน์หลักอย่างน้อยหนึ่งคอลัมน์ เพื่อให้งานนี้สำเร็จ คุณสามารถใช้ฟังก์ชัน INDEX และ MATCH แทนฟังก์ชัน VLOOKUP

 3.8.1 VLOOKUP เพื่อผสานสองตารางตามคอลัมน์หลักเดียว

ตัวอย่างเช่น คุณมีสองตาราง ตารางแรกมีข้อมูลผลิตภัณฑ์และชื่อ และตารางที่สองมีข้อมูลผลิตภัณฑ์และคำสั่งซื้อ ตอนนี้คุณต้องการรวมสองตารางนี้โดยจับคู่คอลัมน์ผลิตภัณฑ์ทั่วไปเป็นตารางเดียว

ขั้นตอนที่ 1: ใช้สูตรต่อไปนี้

โปรดใช้สูตรต่อไปนี้ในเซลล์ว่าง จากนั้นลากที่จับเติมลงไปที่เซลล์ที่คุณต้องการใช้สูตรนี้

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

ผลลัพธ์:

ตอนนี้ คุณจะได้ตารางที่ผสานซึ่งมีคอลัมน์คำสั่งซื้อเข้าร่วมกับตารางแรกตามข้อมูลคอลัมน์หลัก

 3.8.2 VLOOKUP เพื่อผสานสองตารางตามคอลัมน์หลักหลายคอลัมน์

หากตารางสองตารางที่คุณต้องการรวมมีหลายคอลัมน์หลัก หากต้องการรวมตารางตามคอลัมน์ทั่วไปเหล่านี้ โปรดทำตามขั้นตอนด้านล่าง

สูตรทั่วไปคือ:

ขั้นตอนที่ 1: ใช้สูตรต่อไปนี้

โปรดใช้สูตรด้านล่างนี้ในเซลล์ว่างที่คุณต้องการใส่ผลลัพธ์ จากนั้นกดแป้น "Ctrl" + "Shift" + "Enter" พร้อมกันเพื่อรับค่าที่ตรงกันค่าแรก ดูภาพหน้าจอ:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

ขั้นตอนที่ 2: เติมสูตรลงในเซลล์อื่น

จากนั้น เลือกเซลล์สูตรแรก แล้วลากจุดจับเติมเพื่อคัดลอกสูตรนี้ไปยังเซลล์อื่นตามที่คุณต้องการ:

3.9 VLOOKUP จับคู่ค่าในแผ่นงานหลายแผ่น

คุณเคยจำเป็นต้องดำเนินการ VLOOKUP ในแผ่นงานหลายแผ่นใน Excel หรือไม่? ตัวอย่างเช่น ถ้าคุณมีแผ่นงานสามแผ่นที่มีช่วงข้อมูล และคุณต้องการดึงค่าเฉพาะตามเกณฑ์จากแผ่นงานเหล่านี้ คุณสามารถทำตามบทช่วยสอนทีละขั้นตอน ค่า VLOOKUP ในแผ่นงานหลายแผ่น เพื่อให้ภารกิจนี้สำเร็จ

ค่าที่ตรงกันของ VLOOKUP จะเก็บการจัดรูปแบบเซลล์

เมื่อค้นหาค่าที่ตรงกัน การจัดรูปแบบเซลล์ดั้งเดิม เช่น สีฟอนต์ สีพื้นหลัง รูปแบบข้อมูล ฯลฯ จะไม่ถูกเก็บไว้ เพื่อให้เซลล์หรือข้อมูลจัดรูปแบบ ส่วนนี้จะแนะนำเทคนิคบางอย่างในการแก้ปัญหา

4.1 VLOOKUP จับคู่ค่าและเก็บสีเซลล์ การจัดรูปแบบตัวอักษร

อย่างที่เราทราบกันดีว่า ฟังก์ชัน VLOOKUP ปกติสามารถดึงค่าที่ตรงกันจากช่วงข้อมูลอื่นเท่านั้น อย่างไรก็ตาม อาจมีบางกรณีที่คุณต้องการรับค่าที่สอดคล้องกันพร้อมกับการจัดรูปแบบเซลล์ เช่น สีเติม สีฟอนต์ และสไตล์ฟอนต์ ในส่วนนี้ เราจะพูดถึงวิธีการดึงค่าที่ตรงกันในขณะที่รักษาการจัดรูปแบบต้นฉบับใน Excel

โปรดทำตามขั้นตอนต่อไปนี้เพื่อค้นหาและส่งคืนค่าที่เกี่ยวข้องพร้อมกับการจัดรูปแบบเซลล์:

ขั้นตอนที่ 1: คัดลอกโค้ด 1 ลงในโมดูลชีตโค้ด

1. ในเวิร์กชีตที่มีข้อมูลที่คุณต้องการ VLOOKUP ให้คลิกขวาที่แท็บชีตและเลือก "ดูโค้ด" จากเมนูบริบท ดูภาพหน้าจอ:
   
2. ในหน้าต่าง "Microsoft Visual Basic for Applications" ที่เปิดอยู่ โปรดคัดลอกโค้ด VBA ด้านล่างนี้ลงในหน้าต่างโค้ด
3. รหัส VBA 1: VLOOKUP เพื่อรับการจัดรูปแบบเซลล์พร้อมกับค่าการค้นหา

4. Sub Worksheet_Change(ByVal Target As Range)
   'Updateby Extendoffice
       Dim I As Long
       Dim xKeys As Long
       Dim xDicStr As String
       On Error Resume Next
       Application.ScreenUpdating = False
       xKeys = UBound(xDic.Keys)
       If xKeys >= 0 Then
           For I = 0 To UBound(xDic.Keys)
               xDicStr = xDic.Items(I)
               If xDicStr <> "" Then
                   Range(xDic.Keys(I)).Interior.Color = _
                   Range(xDic.Items(I)).Interior.Color
                   Range(xDic.Keys(I)).Font.FontStyle = _
                   Range(xDic.Items(I)).Font.FontStyle
                   Range(xDic.Keys(I)).Font.Size = _
                   Range(xDic.Items(I)).Font.Size
                   Range(xDic.Keys(I)).Font.Color = _
                   Range(xDic.Items(I)).Font.Color
                   Range(xDic.Keys(I)).Font.Name = _
                   Range(xDic.Items(I)).Font.Name
                   Range(xDic.Keys(I)).Font.Underline = _
                   Range(xDic.Items(I)).Font.Underline
               Else
                   Range(xDic.Keys(I)).Interior.Color = xlNone
               End If
           Next
           Set xDic = Nothing
       End If
       Application.ScreenUpdating = True
   End Sub
   

5. 

ขั้นตอนที่ 2: คัดลอกรหัส 2 ลงในหน้าต่างโมดูล

1. ยังคงอยู่ในหน้าต่าง "Microsoft Visual Basic for Applications" คลิก "แทรก" > "โมดูล" จากนั้นคัดลอกโค้ด VBA ด้านล่าง 2 ลงในหน้าต่าง "โมดูล"
2. รหัส VBA 2: VLOOKUP เพื่อรับการจัดรูปแบบเซลล์พร้อมกับค่าการค้นหา

3. Public xDic As New Dictionary
   Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
       Dim xFindCell As Range
       On Error Resume Next
       Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
       If xFindCell Is Nothing Then
           LookupKeepFormat = ""
           xDic.Add Application.Caller.Address, ""
       Else
           LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
           xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
       End If
   End Function
   

4. 

ขั้นตอนที่ 3: เลือกตัวเลือกสำหรับโครงการ VBA

1. หลังจากแทรกโค้ดด้านบนแล้ว ให้คลิก "เครื่องมือ" > "ข้อมูลอ้างอิง" ในหน้าต่าง "Microsoft Visual Basic for Applications" จากนั้นทำเครื่องหมายที่ช่องกาเครื่องหมาย "Microsoft Scripting Runtime" ในกล่องโต้ตอบ "ข้อมูลอ้างอิง – VBAProject" ดูภาพหน้าจอ:
2. จากนั้นคลิก "ตกลง" เพื่อปิดกล่องโต้ตอบ จากนั้นบันทึกและปิดหน้าต่างโค้ด

ขั้นตอนที่ 4: พิมพ์สูตรเพื่อรับผลลัพธ์

1. ตอนนี้ กลับไปที่เวิร์กชีต ใช้สูตรต่อไปนี้ จากนั้นลากที่จับเติมลงเพื่อรับผลลัพธ์ทั้งหมดพร้อมกับการจัดรูปแบบ ดูภาพหน้าจอ:
   

   =LookupKeepFormat(E2,$A$1:$C$10,3)

   
   

4.2 เก็บรูปแบบวันที่จากค่าที่ส่งคืน VLOOKUP

เมื่อใช้ฟังก์ชัน VLOOKUP เพื่อค้นหาและส่งคืนค่าที่มีรูปแบบวันที่ ผลลัพธ์ที่ส่งคืนอาจแสดงเป็นตัวเลข หากต้องการเก็บรูปแบบวันที่ในผลลัพธ์ที่ส่งคืน คุณควรใส่ฟังก์ชัน VLOOKUP ไว้ในฟังก์ชัน TEXT

ขั้นตอนที่ 1: ใช้สูตรต่อไปนี้

โปรดใช้สูตรด้านล่างในเซลล์ว่าง จากนั้นลากที่จับเติมเพื่อคัดลอกสูตรนี้ไปยังเซลล์อื่น

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

ผลลัพธ์:

วันที่ตรงกันทั้งหมดถูกส่งคืนตามภาพด้านล่างที่แสดง:

4.3 ส่งกลับความคิดเห็นของเซลล์จาก VLOOKUP

คุณเคยต้องการดึงข้อมูลเซลล์ที่ตรงกันและความคิดเห็นที่เกี่ยวข้องโดยใช้ VLOOKUP ใน Excel ดังที่แสดงในภาพหน้าจอต่อไปนี้หรือไม่ หากเป็นเช่นนั้น User Defined Function ด้านล่างนี้สามารถช่วยคุณทำงานนี้ให้สำเร็จได้

ขั้นตอนที่ 1: คัดลอกโค้ดลงในโมดูล

1. กดปุ่ม "ALT" + "F11" ค้างไว้เพื่อเปิดหน้าต่าง "Microsoft Visual Basic for Applications"
2. คลิก “แทรก” > “โมดูล” จากนั้นคัดลอกและวางโค้ดต่อไปนี้ลงในหน้าต่าง “โมดูล”
   รหัส VBA: Vlookup และส่งคืนค่าที่ตรงกันพร้อมความคิดเห็นของเซลล์:

   Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
   'Updateby Extendoffice
       Application.Volatile
       Dim xRet As Variant 'could be an error
       Dim xCell As Range
       xRet = Application.Match(LookVal, FTable.Columns(1), FType)
       If IsError(xRet) Then
           VlookupComment = "Not Found"
       Else
           Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
           VlookupComment = xCell.Value
           With Application.Caller
               If Not .Comment Is Nothing Then
                   .Comment.Delete
               End If
               If Not xCell.Comment Is Nothing Then
                   .AddComment xCell.Comment.Text
               End If
           End With
       End If
   End Function

3. จากนั้นบันทึกและปิดหน้าต่างรหัส

ขั้นตอนที่ 2: พิมพ์สูตรเพื่อรับผลลัพธ์

1. ตอนนี้ ให้ป้อนสูตรต่อไปนี้ แล้วลากจุดจับเติมเพื่อคัดลอกสูตรนี้ไปยังเซลล์อื่น มันจะส่งคืนทั้งค่าที่ตรงกันและความคิดเห็นพร้อมกัน ดูภาพหน้าจอ:
   

   =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

   
   

4.4 หมายเลข VLOOKUP ที่จัดเก็บเป็นข้อความ

ตัวอย่างเช่น ฉันมีช่วงของข้อมูลโดยที่หมายเลข ID ในตารางต้นฉบับอยู่ในรูปแบบตัวเลข และหมายเลข ID ในเซลล์ค้นหาถูกจัดเก็บเป็นข้อความ คุณอาจพบข้อผิดพลาด #N/A เมื่อใช้ฟังก์ชัน VLOOKUP ปกติ ในกรณีนี้ หากต้องการดึงข้อมูลที่ถูกต้อง คุณสามารถรวมฟังก์ชัน TEXT และ VALUE ไว้ภายในฟังก์ชัน VLOOKUP ด้านล่างนี้เป็นสูตรเพื่อให้ได้สิ่งนี้:

ขั้นตอนที่ 1: ใช้และกรอกสูตรต่อไปนี้

โปรดใช้สูตรต่อไปนี้ในเซลล์ว่าง จากนั้นลากที่จับเติมลงเพื่อคัดลอกสูตรนี้

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

ผลลัพธ์:

ตอนนี้ คุณจะได้ผลลัพธ์ที่ถูกต้องตามภาพด้านล่างที่แสดง: