Thep Excel
LOOKUPค้นหาข้อมูลแบบดั้งเดิม
LOOKUP ค้นหาค่าในช่วงข้อมูลที่เรียงลำดับแล้ว (ascending) และคืนค่าจากตำแหน่งเดียวกันในอีกช่วงหนึ่ง มีเทคนิคพิเศษคือหาค่าสุดท้ายที่ไม่ว่าง แนะนำใช้ XLOOKUP แทนสำหรับ Excel 365
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| lookup_value | Any | ค่าที่ต้องการค้นหา (LOOKUP จะหาค่าที่น้อยกว่าหรือเท่ากับที่ใกล้เคียงที่สุด) | |
| lookup_vector | Range/Array | ช่วงข้อมูลที่ค้นหา (ต้องเรียงจากน้อยไปมาก) หรือ array 2 มิติสำหรับ Array form | |
| [result_vector]ไม่บังคับ | Range/Array | lookup_vector | ช่วงข้อมูลผลลัพธ์ (ต้องมีขนาดเท่ากับ lookup_vector) ถ้าไม่ระบุจะใช้ lookup_vector |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
LOOKUP ค้นหาค่าในช่วงข้อมูลที่เรียงลำดับแล้ว (ascending) และคืนค่าจากตำแหน่งเดียวกันในอีกช่วงหนึ่ง มี 2 รูปแบบ: Vector (2 ช่วงแยกกัน) และ Array (1 ช่วงหลายคอลัมน์) เทคนิคพิเศษคือหาค่าสุดท้ายที่ไม่ว่าง แนะนำใช้ XLOOKUP แทนสำหรับ Excel 365 ใช้คู่กับ VLOOKUP HLOOKUP INDEX MATCH
How it works
แปลงคะแนนเป็นเกรด
ใช้ LOOKUP หาช่วงคะแนนที่ตรงกับเกรด โดยไม่ต้องใช้ IF ซ้อนกันหลายชั้น
หาค่าสุดท้ายในคอลัมน์
เทคนิคยอดนิยมสำหรับหาข้อมูลล่าสุดในคอลัมน์ที่มีข้อมูลว่าง
Approximate Match
ค้นหาแบบประมาณค่า เช่น หาราคาตามช่วงน้ำหนัก หาค่าคอมมิชชั่นตามยอดขาย
Examples
| A | B | C | D | |
|---|---|---|---|---|
| 1 | รหัสสินค้า | ชื่อสินค้า | สถานะ | ราคา |
| 2 | P001 | เมาส์ไร้สาย | ปิด | 590 |
| 3 | P002 | คีย์บอร์ด | เปิด | 1,290 |
| 4 | P003 | จอ 27 นิ้ว | ปิด | 8,900 |
| 5 | P004 | หูฟัง | เปิด | 2,450 |
| 6 | P005 | เว็บแคม | ปิด | 1,750 |
ทุกสูตรด้านล่างอ้างถึงตารางนี้ พิมพ์ตามได้เลย ผลลัพธ์จะตรงกับที่เขียนไว้
| A | B | |
|---|---|---|
| 1 | คะแนนขั้นต่ำ | เกรด |
| 2 | 0 | F |
| 3 | 50 | D |
| 4 | 60 | C |
| 5 | 70 | B |
| 6 | 80 | A |
LOOKUP บังคับให้คอลัมน์ค้นหาเรียงจากน้อยไปมากเสมอ ไม่มีโหมด Exact Match แยกแบบ VLOOKUP
ตัวอย่างที่ 1: แปลงคะแนนเป็นเกรดLOOKUP(75, F2:F6, G2:G6)
LOOKUP หาค่ามากที่สุดในคอลัมน์คะแนนขั้นต่ำ F2:F6 ที่ยังไม่เกิน 75 ซึ่งคือ 70 แล้วคืนค่าจากตำแหน่งเดียวกันในคอลัมน์เกรด G2:G6 ออกมา
คะแนน 75 จึงได้เกรด B เพราะอยู่ในช่วง 70 ถึงก่อน 80
นี่คือ Vector form ของ LOOKUP ที่ใช้สองช่วงแยกกัน ช่วงแรกไว้ค้นหา ช่วงหลังไว้คืนค่า และคอลัมน์ค้นหาต้องเรียงจากน้อยไปมากเสมอ
ตัวอย่างที่ 2: หาชื่อสินค้ารายการสุดท้ายLOOKUP(2, 1/(B2:B6""), B2:B6)
เทคนิคยอดนิยม 1/(B2:B6<>"") สร้างอาร์เรย์ที่ทุกช่องเป็น 1 เพราะไม่มีเซลล์ไหนว่างเลย
จากนั้น LOOKUP(2, …) หาค่าที่มากที่สุดที่ <= 2 ในอาร์เรย์นั้น ซึ่งทุกค่าเท่ากับ 1 ทำให้ได้ตำแหน่งสุดท้ายของอาร์เรย์เสมอ แล้วคืนค่าจาก B2:B6 ที่ตำแหน่งเดียวกัน ได้ "เว็บแคม" ซึ่งเป็นแถวสุดท้ายของตาราง
เทคนิคนี้ยังใช้ได้แม้มีเซลล์ว่างปนอยู่ตรงกลางคอลัมน์ ซึ่งเป็นเหตุผลที่มันได้รับความนิยมมาก
ตัวอย่างที่ 3: หาตัวเลขสุดท้ายในคอลัมน์LOOKUP(9.99E+307, D:D)
9.99E+307 คือตัวเลขที่ใหญ่เกือบสูงสุดที่ Excel รองรับ มากกว่าตัวเลขจริงในคอลัมน์ D แน่นอน
LOOKUP หาค่าที่มากที่สุดที่ <= 9.99E+307 ในคอลัมน์ D ทั้งคอลัมน์ ซึ่งก็คือตัวเลขตัวสุดท้ายที่เจอ นั่นคือ 1,750 ซึ่งเป็นราคาของแถวสุดท้าย (P005)
เทคนิคนี้ใช้หาตัวเลขล่าสุดในคอลัมน์ที่ยังเพิ่มข้อมูลอยู่เรื่อยๆ ได้โดยไม่ต้องรู้ว่าแถวสุดท้ายอยู่ที่ไหน
ตัวอย่างที่ 4: Array formLOOKUP("P003", A1:D6)
Array form ของ LOOKUP มีอาร์กิวเมนต์เดียวคือช่วงข้อมูล เมื่อช่วงที่ให้มามีแถวมากกว่าคอลัมน์แบบ A1:D6 (6 แถว 4 คอลัมน์) มันจะค้นหาในคอลัมน์แรกและคืนค่าจากคอลัมน์สุดท้ายของแถวเดียวกัน
สูตรนี้ค้นหา "P003" ในคอลัมน์ A แล้วคืนค่าจากคอลัมน์ D (ราคา) ของแถวเดียวกัน ได้ 8,900 บาท
Array form สะดวกตรงไม่ต้องแยกสองช่วง แต่เสี่ยงกว่า Vector form เพราะคืนค่าจากคอลัมน์สุดท้ายเสมอ เปลี่ยนโครงสร้างตารางเมื่อไหร่สูตรพังทันที
ตัวอย่างที่ 5: หาค่าคอมมิชชั่นตามยอดขายLOOKUP(75000, {0,10000,50000,100000}, {0.05,0.07,0.1,0.15})*75000
ช่วงและอัตราคอมมิชชั่นเขียนเป็น array constant ตรงในสูตรเลย ไม่ต้องมีตารางบนชีต
ยอดขาย 75,000 บาท อยู่ในช่วง 50,000 ถึงก่อน 100,000 LOOKUP จึงคืนอัตรา 0.1 (10%) ออกมา แล้วเอาไปคูณยอดขาย 75,000 ต่อในสูตรเดียวกัน ได้คอมมิชชั่น 7,500 บาท
รูปแบบนี้สะดวกเวลาช่วงกับอัตราไม่เปลี่ยนบ่อย เพราะไม่ต้องเปิดตารางอ้างอิงเพิ่ม
ตัวอย่างที่ 6: หารหัสสินค้ารายการสุดท้ายLOOKUP(REPT("z", 255), A:A)
REPT("z", 255) สร้างข้อความ "zzz…z" ยาว 255 ตัวอักษร ซึ่งเรียงหลังข้อความจริงทุกตัวในคอลัมน์แน่นอน
LOOKUP หาค่าข้อความที่มากที่สุดที่ <= ข้อความยักษ์นี้ในคอลัมน์ A ทั้งคอลัมน์ ผลคือได้ข้อความตัวสุดท้ายที่เจอในคอลัมน์นั้น ซึ่งคือรหัส "P005"
เป็นอีกวิธีหนึ่งที่ใช้หาค่าสุดท้ายของคอลัมน์ ใช้แทนเทคนิค 1/(range<>"") ได้เวลาต้องการหาข้อความโดยเฉพาะ
FAQs
LOOKUP ต่างจาก VLOOKUP อย่างไร?+
LOOKUP ค้นหาแบบ approximate match เท่านั้น (ต้องเรียงข้อมูล), VLOOKUP เลือก exact หรือ approximate ได้ และ VLOOKUP ระบุหมายเลขคอลัมน์ผลลัพธ์ได้
ทำไมต้องเรียงข้อมูล?+
LOOKUP ใช้ binary search จึงต้องเรียงข้อมูลจากน้อยไปมาก ถ้าไม่เรียงจะได้ผลลัพธ์ผิด
Vector form กับ Array form ต่างกันอย่างไร?+
Vector form ใช้ 2 ช่วงแยกกัน (lookup และ result), Array form ใช้ 1 ช่วงหลายคอลัมน์ (ค้นหาคอลัมน์แรก คืนคอลัมน์สุดท้าย)
ควรใช้ LOOKUP หรือ XLOOKUP?+
ใช้ XLOOKUP ถ้ามี (Excel 365/2021) เพราะยืดหยุ่นกว่า ใช้ LOOKUP สำหรับความเข้ากันได้กับ Excel เก่าหรือเทคนิคหาค่าสุดท้าย
LOOKUP รองรับ Excel เวอร์ชันไหน?+
ทุกเวอร์ชันตั้งแต่ Excel 2003 เป็นฟังก์ชันพื้นฐานที่มีใน spreadsheet ทุกโปรแกรม
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ LOOKUP จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
CHOOSE เลือกค่าจากรายการค่าตามหมายเลขลำดับที่ระบุ เป็นเหมือนการเลือกรายการจากเมนู ส่งคืนค่าที่ตำแหน่งที่กำหนด มีประโยชน์ในการสร้างสูตรแบบเลือกตามเงื่อนไข
CHOOSECOLS ใช้ดึงคอลัมน์ที่ต้องการจากตารางหรือ Array โดยระบุเลขลำดับคอลัมน์ สามารถดึงได้หลายคอลัมน์พร้อมกัน จัดลำดับใหม่ หรือทำซ้ำคอลัมน์เดิมได้ รองรับการนับคอลัมน์จากขวาสุดโดยใช้เลขลบ (เช่น -1 คือคอลัมน์ขวาสุด)
HLOOKUP ค้นหาข้อมูลจากแถวแรกของตาราง แล้วคืนค่าจากแถวที่ระบุ ตรงข้ามกับ VLOOKUP ที่ค้นหาแนวตั้ง เหมาะสำหรับตารางที่หัวข้อมูลเรียงแบบแนวนอน
INDEX คืนค่าหรือ reference ของเซลล์จากตำแหน่งที่ระบุใน range หรือ array โดยอ้างอิงจากหมายเลขแถวและคอลัมน์ที่เราระบุ มักถูกใช้ร่วมกับ MATCH เพื่อสร้าง INDEX-MATCH pattern ที่ยืดหยุ่นกว่า VLOOKUP เยอะ เพราะสามารถดึงข้อมูลจากทิศทางไหนก็ได้ และไม่พังเมื่อมีการแทรกหรือลบคอลัมน์
MATCH คืนเลขลำดับตำแหน่งของค่าที่ค้นหาในช่วงข้อมูลแถวเดียวหรือคอลัมน์เดียว รองรับการค้นหา 3 โหมด คือ Exact Match (0) ที่ไม่ต้องเรียงข้อมูล, Approximate Match แบบ Less Than or Equal (1) ที่ต้องเรียงจากน้อยไปมาก, และ Greater Than or Equal (-1) ที่ต้องเรียงจากมากไปน้อย รองรับ Wildcard (* และ ?) ในโหมด Exact Match และมักใช้คู่กับ INDEX เป็นรูปแบบ INDEX-MATCH ที่ยืดหยุ่นกว่า VLOOKUP
ฟังก์ชัน RTD ใช้สำหรับดึงข้อมูลแบบ Real-time จากโปรแกรมที่รองรับ COM automation เช่น ราคาหุ้น อัตราแลกเปลี่ยน หรือข้อมูลที่มีการอัพเดทอย่างต่อเนื่อง
.
ที่เจ๋งคือ RTD จะอัพเดทข้อมูลโดยอัตโนมัติเมื่อ Excel อยู่ในโหมดคำนวณอัตโนมัติ ไม่ต้องนั่งกด F9 หรือรีเฟรชด้วยตนเอง ซึ่งเหมาะมากสำหรับการติดตามข้อมูลตลาดการเงิน การวิเคราะห์หุ้น หรือการเชื่อมต่อกับ Data Server ภายนอก
VLOOKUP เป็นฟังก์ชันค้นหาแนวตั้งที่ได้รับความนิยมสูงสุดใน Excel โดยค้นหาค่าที่ต้องการในคอลัมน์ซ้ายสุดของตาราง จากนั้นดึงข้อมูลจากคอลัมน์ที่ระบุในแถวเดียวกัน รองรับทั้งการค้นหาแบบตรงทุกตัวอักษร (Exact Match) และแบบประมาณค่า (Approximate Match) สำหรับข้อมูลที่เรียงลำดับ ข้อจำกัดหลักคือต้องค้นหาจากคอลัมน์ซ้ายสุดเท่านั้น ไม่สามารถดึงข้อมูลจากด้านซ้ายของคอลัมน์ค้นหาได้
ฟังก์ชัน XLOOKUP ใช้สำหรับค้นหาข้อมูลในตารางทั้งแนวตั้งและแนวนอน มีความยืดหยุ่นสูงกว่า VLOOKUP โดยสามารถค้นหาจากซ้ายไปขวา ขวาไปซ้าย และกำหนดค่าเริ่มต้นเมื่อไม่พบข้อมูลได้
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่