ฟังก์ชัน VLOOKUP ของ Excel ใช้เพื่อค้นหาและส่งคืนข้อมูลจากตารางข้อมูลตามค่าการค้นหาที่คุณเลือก
โดยปกติ VLOOKUP ต้องการค่าการค้นหาที่อยู่ในคอลัมน์ด้านซ้ายสุดของตารางข้อมูลและฟังก์ชันจะส่งกลับฟิลด์ข้อมูลอื่นที่อยู่ในแถวเดียวกันทางด้านขวาของค่านี้
อย่างไรก็ตามโดยการรวม VLOOKUP กับฟังก์ชัน CHOOSE คุณสามารถสร้างสูตรการค้นหาด้านซ้ายที่:
- อนุญาตให้ค่าการค้นหามาจากคอลัมน์ใดก็ได้ในตารางข้อมูล
- แสดงข้อมูลที่อยู่ในคอลัมน์ใด ๆ ทางด้านซ้ายของค่าการค้นหา
บันทึก: คำแนะนำเหล่านี้ใช้กับ Excel 2019, 2016, 2013, 2010 และ Excel สำหรับ Office 365
01 จาก 05บทช่วยสอน: การใช้ VLOOKUP และเลือกฟังก์ชั่นในสูตรค้นหาด้านซ้าย
หากต้องการสร้างสูตรการค้นหาด้านซ้ายที่เห็นในภาพตัวอย่างให้ใช้สูตร:
= VLOOKUP ($ D $ 2, CHOOSE ({1,2}, $ F: F $, $ D: $ D), 2, FALSE)
ในตัวอย่างนี้สูตรช่วยให้สามารถหาส่วนที่จัดทำโดย บริษัท อื่น ๆ ที่ระบุไว้ในคอลัมน์ 3 ของตารางข้อมูลได้
งานของฟังก์ชัน CHOOSE ในสูตรคือการหลอกลวงให้ VLOOKUP เชื่อว่าคอลัมน์ที่ 3 เป็นคอลัมน์ที่ 1 ดังนั้นชื่อของ บริษัท จึงสามารถใช้เป็นค่าค้นหาเพื่อหาชื่อของส่วนที่จัดหาโดยแต่ละ บริษัท
ป้อนข้อมูลบทแนะนำ
- ป้อนหัวเรื่อง ผู้ผลิต ลงในเซลล์ D1.
- ป้อนหัวเรื่อง ส่วนหนึ่ง ในเซลล์ E1.
- ป้อนข้อมูลตารางที่เห็นในรูปภาพด้านบนลงในเซลล์ D4 ถึง F9
- แถว 2 และ 3 จะเว้นว่างไว้เพื่อให้เหมาะกับเกณฑ์การค้นหาและสูตรการค้นหาด้านซ้ายที่สร้างขึ้นในระหว่างบทแนะนำนี้
เปิดไดอะล็อกบ็อกซ์ VLOOKUP
แม้ว่าจะเป็นไปได้ที่จะพิมพ์สูตรลงในเซลล์ F1 โดยตรงในแผ่นงาน แต่หลาย ๆ คนมีปัญหากับไวยากรณ์ของสูตร
ในกรณีนี้จะปลอดภัยกว่าการใช้กล่องโต้ตอบ VLOOKUP ฟังก์ชันเกือบทั้งหมดของ Excel มีกล่องโต้ตอบที่ช่วยให้คุณสามารถป้อนอาร์กิวเมนต์แต่ละฟังก์ชันในบรรทัดที่แยกต่างหาก
- คลิกที่เซลล์ E2 ของแผ่นงาน E2 คือตำแหน่งที่จะแสดงผลลัพธ์ของสูตรการค้นหาด้านซ้าย
- คลิกที่ สูตร แถบริบบิ้น
- คลิกที่ ค้นหาและอ้างอิง ตัวเลือกในริบบิ้นเพื่อเปิดรายการแบบเลื่อนลงฟังก์ชั่น
- คลิกที่ VLOOKUP ในรายการเพื่อเปิดกล่องโต้ตอบของฟังก์ชั่น
การป้อนอาร์กิวเมนต์ลงในไดอะล็อกบ็อกซ์ VLOOKUP
อาร์กิวเมนต์ของฟังก์ชันคือค่าที่ใช้โดยฟังก์ชันคำนวณผลลัพธ์
ในกล่องโต้ตอบของฟังก์ชันชื่อของแต่ละอาร์กิวเมนต์อยู่ในบรรทัดที่แยกจากกันตามด้วยเขตข้อมูลที่จะป้อนค่า
ป้อนค่าต่อไปนี้สำหรับอาร์กิวเมนต์ VLOOKUP แต่ละตัวบนบรรทัดที่ถูกต้องของกล่องโต้ตอบตามที่แสดงในภาพประกอบ
ค่าค้นหา
ค่าการค้นหาคือฟิลด์ข้อมูลที่ใช้ในการค้นหาอาร์เรย์ของตาราง VLOOKUP ส่งกลับฟิลด์ข้อมูลอื่นจากแถวเดียวกับค่าการค้นหา
ตัวอย่างนี้ใช้การอ้างอิงเซลล์ไปยังตำแหน่งที่จะป้อนชื่อ บริษัท ลงในแผ่นงาน ประโยชน์ของการนี้คือการทำให้ชื่อ บริษัท เปลี่ยนชื่อได้ง่ายโดยไม่ต้องแก้ไข
- คลิกที่ lookup_value บรรทัดในกล่องโต้ตอบ
- คลิกที่เซลล์ D2 เพื่อเพิ่มการอ้างอิงเซลล์นี้ไปที่ lookup_value เส้น
- กด F4 คีย์บนแป้นพิมพ์เพื่อให้การอ้างอิงเซลล์สมบูรณ์ - $ D $ 2
การอ้างอิงเซลล์สัมบูรณ์
การอ้างอิงเซลล์สัมบูรณ์ถูกใช้สำหรับค่าการค้นหาและอาร์กิวเมนต์อาร์เรย์ของตารางเพื่อป้องกันข้อผิดพลาดหากมีการคัดลอกสูตรการค้นหาไปยังเซลล์อื่นในเวิร์กชีท
การเข้าสู่ฟังก์ชัน CHOOSE
อาร์กิวเมนต์อาร์เรย์ของตารางคือการบล็อกข้อมูลที่ต่อเนื่องจากการเรียกข้อมูลเฉพาะ
โดยปกติแล้ว VLOOKUP จะมองไปทางด้านขวาของอาร์กิวเมนต์ค่าค้นหาเพื่อค้นหาข้อมูลในอาร์เรย์ของตาราง เพื่อให้ดูด้านซ้าย VLOOKUP ต้องหลอกด้วยการจัดเรียงคอลัมน์ใหม่ในอาร์เรย์ของตารางโดยใช้ฟังก์ชัน CHOOSE
ในสูตรนี้ฟังก์ชัน CHOOSE จะทำงานได้สองอย่าง:
- จะสร้างอาร์เรย์ตารางที่มีเพียงสองคอลัมน์กว้าง (คอลัมน์ D และ F)
- เปลี่ยนลำดับจากคอลัมน์ไปทางซ้ายของคอลัมน์ในอาร์เรย์ของตารางเพื่อให้คอลัมน์ F มีค่าเป็นอันดับแรกและคอลัมน์ D เป็นอันดับที่สอง
การเข้าสู่ฟังก์ชั่น
เมื่อป้อนฟังก์ชันด้วยตนเองอาร์กิวเมนต์แต่ละฟังก์ชันจะต้องคั่นด้วยเครื่องหมายคอมมา
- ในกล่องโต้ตอบฟังก์ชัน VLOOKUP ให้คลิกที่ปุ่ม table_array เส้น
- ป้อนต่อไปนี้ เลือก ฟังก์ชั่น:CHOOSE ({1,2}, $ F: F $, $ D: $ D)
หมายเลขดัชนีของคอลัมน์
โดยปกติแล้วหมายเลขดัชนีคอลัมน์จะระบุว่าคอลัมน์ใดของอาร์เรย์ของตารางมีข้อมูลที่คุณอยู่ อย่างไรก็ตามในสูตรนี้หมายถึงลำดับของคอลัมน์ที่กำหนดโดยฟังก์ชัน CHOOSE
ฟังก์ชัน CHOOSE จะสร้างอาร์เรย์ของตารางซึ่งเป็นคอลัมน์สองคอลัมน์กว้าง ๆ พร้อมกับคอลัมน์ F ตามด้วยคอลัมน์ D. เนื่องจากข้อมูลที่ต้องการ - ชื่อส่วน - อยู่ในคอลัมน์ D ค่าของอาร์กิวเมนต์ดัชนีคอลัมน์ต้องตั้งค่าเป็น 2
- คลิกที่ col_index_num บรรทัดในกล่องโต้ตอบ
- พิมพ์ a 2 ในบรรทัดนี้
การค้นหาช่วง
อาร์กิวเมนต์ Range_lookup ของ VLOOKUP เป็นค่าตรรกะ (TRUE หรือ FALSE เท่านั้น) ที่ระบุว่าคุณต้องการให้ VLOOKUP หาค่าที่แน่นอนหรือใกล้เคียงกับค่าการค้นหาหรือไม่
- ถ้า TRUE หรือถ้าอาร์กิวเมนต์นี้ถูกละเว้น VLOOKUP จะส่งกลับค่าที่ตรงกันทั้งหมดไปยัง Lookup_value หรือถ้าไม่พบการจับคู่ที่ตรงกัน VLOOKUP จะส่งค่าที่ใหญ่ที่สุดถัดไป สำหรับสูตรการทำเช่นนี้ข้อมูลในคอลัมน์แรกของ Table_array ต้องเรียงตามลำดับจากน้อยไปมาก
- ถ้า FALSE VLOOKUP ใช้เฉพาะการจับคู่ที่ตรงกับ Lookup_value เท่านั้น หากมีค่าตั้งแต่สองค่าขึ้นไปในคอลัมน์แรกของ Table_array ที่ตรงกับค่าการค้นหาค่าแรกที่พบจะถูกใช้ หากไม่พบการทำงานแบบตรงทั้งหมดระบบจะส่งคืนข้อผิดพลาด # N / A
ในบทแนะนำนี้เนื่องจากเรากำลังมองหาชื่อส่วนหนึ่งไว้ Range_lookup จะถูกตั้งค่าไว้ เท็จ เพื่อให้มีการจับคู่เฉพาะที่ตรงตามสูตร
- คลิกที่ range_lookup บรรทัดในกล่องโต้ตอบ
- พิมพ์คำ เท็จ ในบรรทัดนี้เพื่อระบุว่าเราต้องการให้ VLOOKUP คืนค่าการจับคู่แบบตรงทั้งหมดสำหรับข้อมูลที่เรากำลังหาอยู่
- คลิก ตกลง เพื่อกรอกสูตรการค้นหาด้านซ้ายและปิดกล่องโต้ตอบ
- เนื่องจากเรายังไม่ได้ป้อนชื่อ บริษัท ลงในเซลล์ D2 ข้อผิดพลาด # N / A ปรากฏในเซลล์ E2
การส่งคืนข้อมูลด้วยสูตรการค้นหาจากซ้าย
หากต้องการหา บริษัท ที่จัดหาชิ้นส่วนให้พิมพ์ชื่อ บริษัท ลงในเซลล์ D2 และกดปุ่ม ENTER บนแป้นพิมพ์
ชื่อชิ้นส่วนจะแสดงอยู่ในเซลล์ E2
- คลิกที่เซลล์ D2 ในแผ่นงานของคุณ
- ชนิด Gadgets Plus ลงในเซลล์ D2 และกดปุ่ม ENTER บนแป้นพิมพ์
- ข้อความ "Gadgets" - ส่วนที่จัดทำโดย Gadgets Plus ของ บริษัท - ควรปรากฏในเซลล์ E2
ทดสอบสูตรการค้นหาต่อไปโดยการพิมพ์ชื่อ บริษัท อื่นลงในเซลล์ D2 และชื่อชิ้นส่วนที่ตรงกันควรปรากฏในเซลล์ E2
หากข้อความแสดงข้อผิดพลาดเช่น # N / A ปรากฏในเซลล์ E2 ให้ตรวจหาข้อผิดพลาดในการสะกดผิดในเซลล์ D2
04 จาก 05การสร้างอาร์เรย์ตารางสองคอลัมน์
ไวยากรณ์สำหรับฟังก์ชัน CHOOSE คือ:
= CHOOSE (Index_number, Value1, Value2, … Value254)
ฟังก์ชัน CHOOSE มักจะส่งคืนค่าหนึ่งค่าจากรายการค่า (Value1 to Value254) ตามหมายเลขดัชนีที่ป้อน
ถ้าหมายเลขดัชนีเป็น 1 ฟังก์ชันจะส่งคืน Value1 จากรายการ ถ้าหมายเลขดัชนีเป็น 2 ฟังก์ชันจะส่งกลับ Value2 จากรายการและอื่น ๆ
เมื่อป้อนหมายเลขดัชนีหลายฟังก์ชันจะส่งกลับค่าหลาย ๆ รายการตามลำดับที่ต้องการ การเลือก CHOOSE เพื่อคืนค่าหลายค่าทำได้โดยการสร้างอาร์เรย์
หากต้องการป้อนอาร์เรย์ให้ล้อมรอบตัวเลขที่ป้อนด้วยวงเล็บปีกกาหรือวงเล็บปีกกา ป้อนหมายเลขสองหมายเลขสำหรับดัชนี:{ 1,2 }.
ควรสังเกตว่า CHOOSE ไม่ จำกัด เฉพาะการสร้างตารางสองคอลัมน์ โดยรวมหมายเลขเพิ่มเติมในอาร์เรย์เช่น {1,2,3} - และช่วงเพิ่มเติมในอาร์กิวเมนต์ค่าให้เลือกสร้างตารางสามคอลัมน์
คอลัมน์เพิ่มเติมช่วยให้คุณสามารถส่งคืนข้อมูลที่แตกต่างกันได้ด้วยสูตรการค้นหาด้านซ้ายโดยการเปลี่ยนอาร์กิวเมนต์หมายเลขคอลัมน์ของ VLOOKUP เป็นจำนวนคอลัมน์ที่มีข้อมูลที่ต้องการ
05 จาก 05การเปลี่ยนลำดับของคอลัมน์ด้วยฟังก์ชัน CHOOSE
ในฟังก์ชัน CHOOSE ที่ใช้ในสูตรนี้:
CHOOSE ({1,2}, $ F: F $, $ D: $ D)
ช่วงสำหรับคอลัมน์ F แสดงอยู่ก่อนคอลัมน์ D
เนื่องจากฟังก์ชัน CHOOSE จะกำหนดอาร์เรย์ของ VLOOKUP (แหล่งข้อมูลสำหรับฟังก์ชันนั้น) การเปลี่ยนลำดับของคอลัมน์ในฟังก์ชัน CHOOSE จะถูกส่งผ่านไปยัง VLOOKUP
เท่าที่ VLOOKUP เกี่ยวข้องตารางอาร์เรย์มีเพียงสองคอลัมน์กว้างกับคอลัมน์ F ด้านซ้ายและคอลัมน์ D ด้านขวา เนื่องจากคอลัมน์ F มีชื่อ บริษัท ที่เราต้องการค้นหาและตั้งแต่ D คอลัมน์มีชื่อส่วน VLOOKUP สามารถทำหน้าที่การค้นหาตามปกติในการค้นหาข้อมูลที่อยู่ทางด้านซ้ายของค่าการค้นหา
ด้วยเหตุนี้ VLOOKUP สามารถใช้ชื่อ บริษัท เพื่อค้นหาส่วนที่จัดหาได้