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

ฟังก์ชัน VLOOKUP เป็นเกณฑ์มาตรฐาน

ถ้าคุณรู้วิธีใช้ฟังก์ชัน VLOOKUP คุณก็รู้บางอย่างเกี่ยวกับ Excel

ถ้าคุณไม่ทำ คุณไม่ควรระบุ Excel ว่าเป็นหนึ่งในจุดแข็งของคุณในประวัติย่อของคุณ

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

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

นี่จะเป็นบทช่วยสอน VLOOKUP ขนาดใหญ่ (ตามมาตรฐานของฉัน)

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

เมื่อใดควรใช้ฟังก์ชัน VLOOKUP ใน Excel

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

มาดูตัวอย่างง่ายๆ ที่นี่ เพื่อทำความเข้าใจว่าเมื่อใดควรใช้ Vlookup ใน Excel

อย่าลืมว่าทุกคนเคยค้นหาชื่อและคะแนนของตนอย่างบ้าคลั่งเมื่อคะแนนสอบออกมาและติดอยู่บนกระดานข่าว (อย่างน้อยนั่นคือสิ่งที่เกิดขึ้นบ่อยมากตอนที่ฉันอยู่ในโรงเรียน)

นี่คือวิธีการทำงาน:

• คุณไปที่กระดานข่าวและเริ่มค้นหาชื่อหรือหมายเลขทะเบียนของคุณ (เลื่อนนิ้วขึ้นและลงรายการ)
• เมื่อคุณพบชื่อของคุณแล้ว คุณจะต้องเลื่อนตาไปทางด้านขวาของชื่อ/หมายเลขทะเบียนเพื่อดูคะแนนของคุณ

นั่นคือสิ่งที่ฟีเจอร์ Excel VLOOKUP ทำเพื่อคุณ (โปรดใช้ตัวอย่างนี้ในการสัมภาษณ์ครั้งต่อไปของคุณได้ตามสบาย)

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

ไวยากรณ์

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

พารามิเตอร์อินพุต

• lookup_value –นี่คือค่าการค้นหาที่คุณพยายามค้นหาในคอลัมน์ซ้ายสุดของตารางอาจเป็นค่า การอ้างอิงเซลล์ หรือสตริงข้อความในตัวอย่างตารางสรุปสถิติ นี่คือชื่อของคุณ
• table_array –นี่คืออาร์เรย์ของตารางที่คุณกำลังมองหาค่านี่อาจเป็นการอ้างอิงถึงช่วงเซลล์หรือช่วงที่มีชื่อในตัวอย่างตารางคะแนน นี่จะเป็นทั้งตารางที่มีคะแนนของทุกคนในแต่ละวิชา
• col_ดัชนี –นี่คือหมายเลขดัชนีคอลัมน์ที่คุณต้องการรับค่าที่ตรงกันในตัวอย่างตารางเศษส่วน ถ้าคุณต้องการเศษส่วนของคณิตศาสตร์ (ซึ่งเป็นคอลัมน์แรกในตารางที่มีเศษส่วน) คุณสามารถดูคอลัมน์ที่ 1หากคุณต้องการคะแนนทางกายภาพ คุณสามารถดูคอลัมน์ที่ 1 และ 2
• [range_lookup] -ที่นี่คุณสามารถระบุได้ว่าคุณต้องการการจับคู่แบบตรงทั้งหมดหรือการจับคู่โดยประมาณหากไม่ระบุ ค่าเริ่มต้นจะเป็น TRUE - ค่าที่ตรงกันโดยประมาณ(ดูหมายเหตุเพิ่มเติมด้านล่าง)

หมายเหตุเพิ่มเติม (น่าเบื่อ แต่สำคัญที่ต้องรู้)

• การจับคู่อาจเป็นแบบตรงทั้งหมด (FALSE หรือ 0 ใน range_lookup) หรือค่าประมาณ (TRUE หรือ 1)
• ในการค้นหาโดยประมาณ ตรวจสอบให้แน่ใจว่ารายการถูกเรียงลำดับจากน้อยไปมาก (บนลงล่าง) มิฉะนั้น ผลลัพธ์อาจไม่ถูกต้อง
• เมื่อ range_lookup เป็น TRUE (การค้นหาโดยประมาณ) และข้อมูลจะถูกเรียงลำดับจากน้อยไปมาก:
  • หากฟังก์ชัน VLOOKUP ไม่พบค่า ระบบจะส่งกลับค่าที่มากที่สุดซึ่งน้อยกว่า lookup_value
  • ส่งกลับข้อผิดพลาด #N/A ถ้า lookup_value น้อยกว่าค่าต่ำสุด
  • หาก lookup_value เป็นข้อความ คุณสามารถใช้อักขระตัวแทนได้ (ดูตัวอย่างด้านล่าง)

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

10 ตัวอย่าง Excel VLOOKUP (พื้นฐานและขั้นสูง)

ต่อไปนี้คือตัวอย่าง 10 ตัวอย่างที่มีประโยชน์ของการใช้ Excel Vlookup เพื่อแสดงวิธีใช้งานในชีวิตประจำวันของคุณ

ตัวอย่างที่ 1 - ค้นหาคะแนนคณิตศาสตร์ของแบรด

ในตัวอย่าง VLOOKUP ด้านล่าง ฉันมีรายชื่อนักเรียนในคอลัมน์ซ้ายสุดและเกรดสำหรับวิชาต่างๆ ในคอลัมน์ B ถึง E

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

จากข้อมูลข้างต้น ฉันต้องรู้ว่าแบรดทำคะแนนทางคณิตศาสตร์ได้อย่างไร

นี่คือสูตร VLOOKUP ที่ส่งกลับคะแนนคณิตศาสตร์ของ Brad:

=VLOOKUP("แบรด",$A$3:$E$10,2,0)

สูตรข้างต้นมีสี่พารามิเตอร์:

• "แบรด": - นี่คือค่าการค้นหา
• $A$3:$E$10 - นี่คือช่วงของเซลล์ที่เรากำลังดูอยู่จำไว้ว่า Excel จะค้นหาค่าการค้นหาในคอลัมน์ซ้ายสุดในตัวอย่างนี้ จะค้นหาชื่อ Brad ใน A3:A10 ซึ่งเป็นคอลัมน์ซ้ายสุดของอาร์เรย์ที่ระบุ
• 2 – เมื่อฟังก์ชันพบชื่อของ Brad มันจะไปที่คอลัมน์ที่สองของอาร์เรย์และส่งกลับค่าในแถวเดียวกับ Bradค่า 2 ในที่นี้หมายความว่าเรากำลังมองหาคะแนนจากคอลัมน์ที่สองของอาร์เรย์ที่ระบุ
• 0 – ซึ่งจะบอกให้ฟังก์ชัน VLOOKUP ค้นหาเฉพาะรายการที่ตรงกันเท่านั้น

นี่คือวิธีการทำงานของสูตร VLOOKUP ในตัวอย่างด้านบน

อันดับแรก ค้นหาค่า Brad ในคอลัมน์ซ้ายสุดค้นหาค่าในเซลล์ A6 จากบนลงล่าง

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

คุณสามารถใช้การสร้างสูตรเดียวกันเพื่อให้ได้คะแนนของใครก็ได้ในวิชาใดก็ได้

ตัวอย่างเช่น ในการหาคะแนนของ Maria ในวิชาเคมี ให้ใช้สูตร VLOOKUP ต่อไปนี้:

=VLOOKUP("มาเรีย",$A$3:$E$10,4,0)

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

ประโยชน์ของการใช้การอ้างอิงเซลล์คือทำให้สูตรเป็นแบบไดนามิก

ตัวอย่างเช่น หากคุณมีเซลล์ที่มีชื่อนักเรียนและคุณกำลังได้คะแนนวิชาคณิตศาสตร์ เมื่อคุณเปลี่ยนชื่อนักเรียน ผลลัพธ์จะอัปเดตโดยอัตโนมัติ (ดังที่แสดงด้านล่าง):

หากคุณป้อนค่าการค้นหาที่ไม่พบในคอลัมน์ซ้ายสุด ข้อผิดพลาด #N/A จะถูกส่งกลับ

ตัวอย่างที่ 2 - การค้นหาแบบสองทาง

ในตัวอย่างที่ 1 ด้านบน เราได้ฮาร์ดโค้ดค่าคอลัมน์ดังนั้นสูตรจะคืนค่าคะแนนคณิตศาสตร์เสมอเพราะเราใช้ 2 เป็นหมายเลขดัชนีของคอลัมน์

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

นี่คือตัวอย่างของฟังก์ชัน VLOOKUP แบบสองทิศทาง

ในการสร้างสูตรการค้นหาแบบสองทางนี้ คุณต้องทำให้คอลัมน์เป็นไดนามิกด้วยดังนั้นเมื่อผู้ใช้เปลี่ยนหัวเรื่อง สูตรจะเลือกคอลัมน์ที่ถูกต้องโดยอัตโนมัติ (2 สำหรับคณิตศาสตร์ 3 สำหรับฟิสิกส์ ฯลฯ..)

ในการดำเนินการนี้ คุณต้องใช้ฟังก์ชัน MATCH เป็นพารามิเตอร์ของคอลัมน์

นี่คือสูตร VLOOKUP ที่จะทำสิ่งนี้:

=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

สูตรข้างต้นใช้ MATCH(H3,$A$2:$E$2,0) เป็นหมายเลขคอลัมน์ฟังก์ชัน MATCH ใช้ชื่อหัวข้อเป็นค่าการค้นหา (ใน H3) และส่งกลับตำแหน่งใน A2:E2ดังนั้นหากคุณใช้คณิตศาสตร์ ค่าจะส่งกลับ 2 เนื่องจากพบคณิตศาสตร์ใน B2 (ซึ่งเป็นเซลล์ที่สองในช่วงอาร์เรย์ที่ระบุ)

ตัวอย่างที่ 3 - การใช้ดรอปดาวน์เป็นค่าการค้นหา

ในตัวอย่างข้างต้น เราต้องป้อนข้อมูลด้วยตนเองการดำเนินการนี้อาจใช้เวลานานและเกิดข้อผิดพลาดได้ โดยเฉพาะอย่างยิ่งหากคุณมีค่าการค้นหาจำนวนมาก

ในกรณีนี้ ความคิดที่ดีคือการสร้างรายการดรอปดาวน์ของค่าการค้นหา (ในกรณีนี้อาจเป็นชื่อและหัวเรื่องของนักเรียน) แล้วเลือกจากรายการ

สูตรจะอัปเดตผลลัพธ์โดยอัตโนมัติทั้งนี้ขึ้นอยู่กับการเลือก

ดังที่แสดงด้านล่าง:

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

ฉันจะทำสิ่งนี้ได้อย่างไร:

สูตร VLOOKUP ที่ใช้ในตัวอย่างนี้จะเหมือนกับที่ใช้ในตัวอย่างที่ 2

=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

ค่าการค้นหาถูกแปลงเป็นรายการดรอปดาวน์

นี่คือขั้นตอนในการสร้างรายการดรอปดาวน์:

• เลือกเซลล์ที่ต้องการรายการดรอปดาวน์ในตัวอย่างนี้ ใน G4 เราต้องการชื่อนักเรียน
• ไปที่ข้อมูล -> เครื่องมือข้อมูล -> การตรวจสอบข้อมูล
• บนแท็บการตั้งค่าของกล่องโต้ตอบการตรวจสอบข้อมูล เลือกรายการจากรายการดรอปดาวน์อนุญาต
• ในแหล่งที่มา เลือก $A$3:$A$10
• คลิกตกลง

ตอนนี้คุณจะเห็นรายการดรอปดาวน์ในเซลล์ G4ในทำนองเดียวกัน คุณสามารถสร้างได้ใน H3 สำหรับธีม

ตัวอย่างที่ 4 - ค้นหาสามทาง

การค้นหาแบบสามทางคืออะไร

ในตัวอย่างที่ 2 เราใช้ตารางค้นหาที่มีคะแนนสำหรับนักเรียนในวิชาต่างๆนี่คือตัวอย่างการค้นหาแบบสองทาง เนื่องจากเราใช้ตัวแปรสองตัวเพื่อให้ได้คะแนน (ชื่อนักเรียนและชื่อเรื่อง)

สมมติว่าในหนึ่งปี นักเรียนจะต้องสอบสามระดับ สอบหน่วย สอบกลางภาค และสอบปลายภาค (นี่เป็นข้อสอบสมัยเรียนของฉัน)

การค้นหาแบบสามทางจะสามารถรับคะแนนของนักเรียนในเรื่องที่ระบุจากระดับการสอบที่ระบุ

ดังที่แสดงด้านล่าง:

ในตัวอย่างข้างต้น ฟังก์ชัน VLOOKUP สามารถค้นหาและส่งกลับเกรดของนักเรียนที่ระบุในวิชาที่ระบุในตารางที่แตกต่างกันสามตาราง (Unit Tests, Midterms และ Finals)

นี่คือสูตรที่ใช้ในเซลล์ H4:

=VLOOKUP(G4,CHOOSE(IF(H2="Unit Test",1,IF(H2="Midterm",2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23),MATCH(H3,$A$2:$E$2,0),0)

สูตรนี้ใช้ฟังก์ชัน CHOOSE เพื่อให้แน่ใจว่ามีการอ้างอิงตารางที่ถูกต้องมาวิเคราะห์ส่วน CHOOSE ของสูตรกัน:

CHOOSE(IF(H2=”Unit Test”,1,IF(H2=”Midterm”,2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23)

อาร์กิวเมนต์แรกของสูตรคือ IF(H2=”Unit Test”,1,IF(H2=”Midterm”,2,3)) ซึ่งจะตรวจสอบเซลล์ H2 และเห็นระดับการทดสอบที่อ้างอิงหากเป็นการทดสอบหน่วย จะมีการคืนค่า $A$3:$E$7 ซึ่งประกอบด้วยคะแนนการทดสอบหน่วยคืนค่า $A$11:$E$15 หากเป็นการสอบกลางภาค มิฉะนั้นจะคืนค่า $A$19:$E$23

การทำเช่นนี้จะทำให้อาร์เรย์ตาราง VLOOKUP เป็นไดนามิก ทำให้เป็นการค้นหาแบบสามทาง

ตัวอย่างที่ 5 - รับค่าสุดท้ายจากรายการ

คุณสามารถสร้างสูตร VLOOKUP เพื่อรับค่าสุดท้ายในรายการ

จำนวนบวกที่ใหญ่ที่สุดที่คุณสามารถใช้ใน Excel ได้คือ 9.99999999999999E + 307 . ซึ่งหมายความว่าหมายเลขการค้นหาที่ใหญ่ที่สุดในหมายเลข VLOOKUP จะเหมือนกัน

ฉันไม่คิดว่าคุณจะต้องมีการคำนวณใด ๆ ที่เกี่ยวข้องกับตัวเลขจำนวนมากเช่นนี้นี่คือสิ่งที่เราใช้เพื่อรับหมายเลขสุดท้ายในรายการ

สมมติว่าคุณมีชุดข้อมูลดังนี้(ใน A1:A14)และคุณต้องการรับหมายเลขสุดท้ายในรายการ

นี่คือสูตรที่คุณสามารถใช้ได้:

=VLOOKUP(9.99999999999999E+307,$A$1:$A$14,จริง)

โปรดทราบว่าสูตรข้างต้นใช้ VLOOKUP . ที่ตรงกันโดยประมาณ  (หมายเหตุ TRUE ที่ท้ายสูตร ไม่ใช่ FALSE หรือ 0)นอกจากนี้ โปรดทราบว่าสูตร VLOOKUP นี้ทำงานโดยไม่ต้องเรียงลำดับรายการ

นี่คือวิธีการทำงานของฟังก์ชัน VLOOKUP โดยประมาณจะสแกนคอลัมน์ซ้ายสุดจากบนลงล่าง

• ค่านี้จะถูกส่งกลับหากพบการจับคู่แบบตรงทั้งหมด
• หากพบค่าที่สูงกว่าค่าการค้นหา ระบบจะส่งกลับค่าในเซลล์ด้านบน
• ถ้าค่าการค้นหามากกว่าค่าทั้งหมดในรายการ ค่าสุดท้ายจะถูกส่งคืน

ในตัวอย่างข้างต้น กรณีที่สามอยู่ในที่ทำงาน

เนื่องจาก9.99999999999999E + 307เป็นตัวเลขที่ใหญ่ที่สุดที่สามารถใช้ใน Excel ได้ ดังนั้นเมื่อถูกใช้เป็นค่าการค้นหา ก็จะส่งกลับตัวเลขสุดท้ายในรายการ

คุณยังสามารถใช้เพื่อส่งคืนรายการข้อความสุดท้ายในรายการได้อีกด้วยนี่คือสูตรที่สามารถทำได้:

=VLOOKUP("zzz",$A$1:$A$8,1, TRUE )

ทำตามตรรกะเดียวกันExcel จะดูชื่อทั้งหมด และเนื่องจาก zzz ถือว่ามีขนาดใหญ่กว่าชื่อ/ข้อความใดๆ ที่ขึ้นต้นด้วยตัวอักษรก่อน zzz โปรแกรมจะส่งคืนรายการสุดท้ายในรายการ

ตัวอย่างที่ 6 - การค้นหาบางส่วนโดยใช้สัญลักษณ์แทนและ VLOOKUP

สัญลักษณ์ตัวแทนของ Excel มีประโยชน์ในหลาย ๆ สถานการณ์

ยาวิเศษนี้ช่วยให้สูตรของคุณมีพลังพิเศษ

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

ตัวอย่างเช่น สมมติว่าคุณมีชุดข้อมูลดังต่อไปนี้ และคุณต้องการค้นหาบริษัท ABC ในรายการ แต่รายการมี ABC Ltd แทนที่จะเป็น ABC

คุณไม่สามารถใช้ ABC เป็นค่าการค้นหาได้ เนื่องจากไม่มีค่าที่ตรงกันทุกประการในคอลัมน์ Aการจับคู่โดยประมาณอาจนำไปสู่ผลลัพธ์ที่ไม่ถูกต้อง และรายการต้องเรียงลำดับจากน้อยไปมาก

อย่างไรก็ตาม คุณสามารถใช้ไวด์การ์ดในฟังก์ชัน VLOOKUP เพื่อรับการจับคู่

ป้อนสูตรต่อไปนี้ในเซลล์ D2 แล้วลากไปยังเซลล์อื่น:

=VLOOKUP("*"&C2&"*",$A$2:$A$8,1,FALSE)

สูตรนี้ทำงานอย่างไร?

ในสูตรข้างต้น แทนที่จะใช้ค่าการค้นหาตามที่เป็นอยู่ มันถูกล้อมรอบด้วยเครื่องหมายดอกจัน (*) - "*"&C2&"*"

เครื่องหมายดอกจันคืออักขระตัวแทนใน Excel ที่สามารถแสดงอักขระจำนวนเท่าใดก็ได้

ใช้เครื่องหมายดอกจันรอบๆ ค่าการค้นหาเพื่อบอก Excel ว่าจำเป็นต้องค้นหาข้อความใดๆ ที่มีคำดังกล่าวใน C2สามารถมีอักขระได้จำนวนเท่าใดก็ได้ก่อนหรือหลังข้อความใน C2

ตัวอย่างเช่น เซลล์ C2 มี ABC ดังนั้นฟังก์ชัน VLOOKUP จะดูที่ชื่อใน A2:A8 และค้นหา ABCพบการจับคู่ในเซลล์ A2 เนื่องจากมี ABC จาก ABC Ltd. ไม่สำคัญว่าจะมีอักขระใดๆ ทางด้านซ้ายหรือขวาของ ABC หรือไม่จะถือว่าตรงกันจนกว่าจะมี ABC ในสตริงข้อความ

หมายเหตุ: ฟังก์ชัน VLOOKUP จะคืนค่าที่ตรงกันเป็นค่าแรกเสมอ และหยุดการค้นหาเพิ่มเติมดังนั้นหากมี ABC อยู่ในรายการ จำกัดและ ABC Corporation จะคืนค่าแรกและไม่สนใจส่วนที่เหลือ

ตัวอย่างที่ 7 - VLOOKUP ส่งกลับข้อผิดพลาดแม้จะจับคู่ค่าการค้นหา

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

ตัวอย่างเช่น ในกรณีด้านล่าง มีการจับคู่ (ด้าน) แต่ฟังก์ชัน VLOOKUP ยังคงส่งกลับข้อผิดพลาด

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

ซึ่งมักจะเกิดขึ้นเมื่อคุณนำเข้าข้อมูลจากฐานข้อมูลหรือรับข้อมูลจากบุคคลอื่นช่องว่างนำหน้า/ต่อท้ายเหล่านี้มีแนวโน้มที่จะคืบคลานเข้ามา

วิธีแก้ไขคือฟังก์ชัน TRIMโดยจะลบช่องว่างนำหน้าหรือต่อท้ายหรือช่องว่างพิเศษระหว่างคำ

นี่คือสูตรที่จะให้ผลลัพธ์ที่ถูกต้องแก่คุณ

=VLOOKUP("แมตต์",TRIM($A$2:$A$9),1,0)

เนื่องจากเป็นสูตรอาร์เรย์ ให้ใช้ Control+Shift+Enter แทน Enter

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

ตัวอย่างที่ 8 - การค้นหาแบบตรงตามตัวพิมพ์ใหญ่และตัวพิมพ์ใหญ่

ตามค่าเริ่มต้น ค่าการค้นหาในฟังก์ชัน VLOOKUP จะไม่คำนึงถึงขนาดตัวพิมพ์ตัวอย่างเช่น หากค่าการค้นหาของคุณคือ MATT, matt หรือ Matt ฟังก์ชัน VLOOKUP จะเหมือนกันทั้งหมดส่งคืนค่าที่ตรงกันครั้งแรกโดยไม่คำนึงถึงขนาดตัวพิมพ์

แต่ถ้าคุณต้องการการค้นหาแบบคำนึงถึงขนาดตัวพิมพ์ คุณต้องใช้ฟังก์ชัน EXACT และฟังก์ชัน VLOOKUP

นี่คือตัวอย่าง:

อย่างที่คุณเห็น เซลล์ทั้งสามมีชื่อเหมือนกัน (ใน A2, A4 และ A5) แต่ใช้อักษรตัวพิมพ์ใหญ่ต่างกันทางด้านขวา เรามีสามชื่อ (Matt, MATT และ Matt) และคะแนนทางคณิตศาสตร์

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

เพื่อให้พิจารณาตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ เราจำเป็นต้องใช้คอลัมน์ตัวช่วย (ดังแสดงด้านล่าง):

หากต้องการรับค่าในคอลัมน์วิธีใช้ ให้ใช้ฟังก์ชัน =ROW()จะได้รับเฉพาะหมายเลขแถวในเซลล์

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

=VLOOKUP(MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))),$B$2:$C$9,2,0)

ตอนนี้ มาทำลายมันและทำความเข้าใจว่ามันทำอะไร:

• EXACT(E2,$A$2:$A$9) – ส่วนนี้เปรียบเทียบค่าการค้นหาใน E2 กับค่าทั้งหมดใน A2:A9ส่งกลับอาร์เรย์ของ TRUE/FALSE โดยที่ TRUE จะถูกส่งกลับในกรณีที่มีการจับคู่แบบตรงทั้งหมดในกรณีนี้จะส่งคืนอาร์เรย์ต่อไปนี้: {TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
• EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9) - ส่วนนี้คูณอาร์เรย์ TRUE/FALSE ด้วยหมายเลขแถว ตราบใดที่มีค่า TRUE ก็จะให้หมายเลขแถว มิฉะนั้นจะให้ 0 ในกรณีนี้จะส่งกลับ {2;0;0;0;0;0;0;0}
• MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))) – 這部分返回數字數組中的最大值。ในกรณีนี้จะส่งกลับ 2 (ซึ่งเป็นหมายเลขบรรทัดที่แน่นอน)
• ตอนนี้เราใช้ตัวเลขนี้เป็นค่าการค้นหาและใช้อาร์เรย์การค้นหาเป็น B2:C9

หมายเหตุ: เนื่องจากนี่คือสูตรอาร์เรย์ ให้ใช้ Control + Shift + Enter แทนการพิมพ์เพียงอย่างเดียว

ตัวอย่างที่ 9 - การใช้ VLOOKUP กับหลายเงื่อนไข

ฟังก์ชัน Excel VLOOKUP ในรูปแบบพื้นฐานจะค้นหาค่าการค้นหาและส่งกลับค่าที่สอดคล้องกันจากแถวที่ระบุ

แต่โดยปกติ VLOOKUP จำเป็นใน Excel ที่มีหลายเกณฑ์

สมมติว่าคุณมีข้อมูลที่มีชื่อนักเรียน ประเภทการทดสอบ และคะแนนคณิตศาสตร์ (แสดงด้านล่าง):

การใช้ฟังก์ชัน VLOOKUP เพื่อให้ได้คะแนนคณิตศาสตร์ของนักเรียนแต่ละคนในระดับการทดสอบนั้นอาจเป็นเรื่องที่ท้าทาย

ตัวอย่างเช่น หากคุณพยายามใช้ VLOOKUP โดยมี Matt เป็นค่าการค้นหา จะส่งคืนค่า 91 เสมอ ซึ่งเป็นคะแนนสำหรับการเกิดขึ้นครั้งแรกของ Matt ในรายการเพื่อให้ได้คะแนน Matt สำหรับการสอบแต่ละประเภท (หน่วย กลางภาค และปลายภาค) คุณต้องสร้างค่าการค้นหาที่ไม่ซ้ำกัน

ซึ่งสามารถทำได้โดยใช้คอลัมน์ตัวช่วยขั้นตอนแรกคือการแทรกคอลัมน์ตัวช่วยทางด้านซ้ายของเศษส่วน

ตอนนี้ ในการสร้างตัวระบุเฉพาะสำหรับแต่ละอินสแตนซ์ของชื่อ ให้ใช้สูตรต่อไปนี้ใน C2: =A2&”|”&B2

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

ตอนนี้ แม้ว่าจะมีชื่อซ้ำกัน แต่เมื่อรวมชื่อกับระดับการสอบแล้ว ก็ไม่มีชื่อซ้ำกัน

ซึ่งง่ายเพราะตอนนี้คุณสามารถใช้ค่าคอลัมน์ตัวช่วยเป็นค่าการค้นหาได้

นี่คือสูตรที่ให้ผลลัพธ์ใน G3:I8

=VLOOKUP($F3&"|"&G$2,$C$2:$D$19,2,0)

ที่นี่ เรารวมชื่อนักเรียนและระดับการสอบเพื่อรับค่าการค้นหา เราใช้ค่าการค้นหานี้เพื่อตรวจสอบในคอลัมน์ตัวช่วยสำหรับเรกคอร์ดที่ตรงกัน

หมายเหตุ: ในตัวอย่างข้างต้น เราใช้ | ใช้เป็นตัวคั่นเมื่อเพิ่มข้อความลงในคอลัมน์เสริมในบางกรณีที่หายากมาก (แต่เป็นไปได้) คุณอาจมีเกณฑ์สองแบบที่แตกต่างกัน แต่การรวมเกณฑ์เหล่านี้เข้าด้วยกันจะทำให้ได้ผลลัพธ์ที่เหมือนกันนี่คือตัวอย่าง:

โปรดทราบว่าแม้ว่า A2 และ A3 จะแตกต่างกันและ B2 และ B3 ต่างกัน แต่การรวมกันในท้ายที่สุดก็เหมือนกันอย่างไรก็ตาม หากคุณใช้ตัวคั่น แม้แต่ชุดค่าผสมก็จะต่างกัน (D2 และ D3)

นี่คือบทช่วยสอนเกี่ยวกับวิธีใช้ VLOOKUP ที่มีหลายเงื่อนไขโดยไม่ต้องใช้คอลัมน์ตัวช่วย

ตัวอย่างที่ 10 - การจัดการข้อผิดพลาดเมื่อใช้ฟังก์ชัน VLOOKUP

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

คุณสามารถลบค่าความผิดพลาดได้อย่างง่ายดายด้วยข้อความเต็มของความหมาย เช่น "ไม่มี" หรือ "ไม่พบ"

ตัวอย่างเช่น ในตัวอย่างด้านล่าง เมื่อคุณพยายามค้นหาคะแนนของ Brad ในรายการ จะส่งคืนข้อผิดพลาดเนื่องจากชื่อของ Brad ไม่อยู่ในรายการ

หากต้องการลบข้อผิดพลาดนี้และแทนที่ด้วยสิ่งที่มีความหมาย ให้รวมฟังก์ชัน VLOOKUP ในฟังก์ชัน IFERROR

นี่คือสูตร:

=IFERROR(VLOOKUP(D2,$A$2:$B$7,2,0),"ไม่พบ")

ฟังก์ชัน IFERROR จะตรวจสอบว่าค่าที่ส่งคืนโดยอาร์กิวเมนต์แรก (ในกรณีนี้คือฟังก์ชัน VLOOKUP) มีข้อผิดพลาดหรือไม่หากไม่ใช่ข้อผิดพลาด ให้คืนค่าผ่านฟังก์ชัน VLOOKUP ไม่เช่นนั้นให้คืนค่า Not Found

ฟังก์ชัน IFERROR ใช้งานได้ตั้งแต่ Excel 2007หากคุณกำลังใช้เวอร์ชันก่อนหน้า โปรดใช้ฟังก์ชันต่อไปนี้:

=IF(ISERROR(VLOOKUP(D2,$A$2:$B$7,2,0)),"Not Found",VLOOKUP(D2,$A$2:$B$7,2,0))

นั่นคือทั้งหมดสำหรับบทช่วยสอน VLOOKUP นี้

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

ฟังก์ชัน Excel ที่เกี่ยวข้อง:

• ฟังก์ชัน Excel HLOOKUP
• ฟังก์ชัน Excel XLOOKUP
• ฟังก์ชันดัชนีของ Excel
• ฟังก์ชันทางอ้อมของ Excel
• ฟังก์ชันจับคู่ Excel
• ฟังก์ชันออฟเซ็ตของ Excel