“อยากดึงข้อมูลตัวสุดท้าย ต้องเขียนสูตรยังไงครับ?” ตั้มโพล่งขึ้น
“ใช้ VLOOKUP แบบ Approximate Match ไม่ได้เหรอ?” ผมถามกลับ
“ผมลองแล้ว มันดึงค่ามาผิดครับ”
“เอ๋!”
ข้อมูลที่ตั้มมีคือ บันทึกการขายของแต่ละประเทศ (A2:C8)
สิ่งที่ต้องการคือ เขียนสูตรที่เซลล์ G2 เพื่อดึงข้อมูลค่าสุดท้ายของ Thailand
ผลลัพธ์ที่ควรจะเป็นคือ 600

ถ้าใช้ VLOOKUP แบบ Approximate Match โดยเขียนสูตรเป็น
=VLOOKUP(F2,$B$2:$C$8,2,1)
ผลลัพธ์ที่ได้คือ 400 ซึ่งผิด!

ทำไมจึงเป็นเช่นนั้น?
เพราะ VLOOKUP แบบ Approximate Match ใช้ได้ในกรณีที่ข้อมูลเรียงกันจากน้อยไปมากเท่านั้น
เช่นเคสนี้ ถ้าเรียงข้อมูลตามชื่อประเทศ จะได้ผลลัพธ์ตามต้องการ

“แต่ผมเรียงข้อมูลแบบนั้นไม่ได้ครับ” ตั้มแย้ง
“ทำไมล่ะ?” ผมถามกลับ
“เพราะข้อมูลจากระบบเรียงตามวันที่ ผมเปลี่ยนข้อมูลจากระบบไม่ได้ครับ”
“อ้อ”
“พี่พอมีวิธีเขียนสูตรเพื่อดึงข้อมูลตัวสุดท้ายไหมครับ?”
“มีสิ มีตั้ง 4 วิธีเลยนะ”
“4 วิธีเลยหรือครับ!” ตั้มโพล่งเสียงดัง
หมายเหตุ: บทความนี้มีเนื้อหาประยุกต์ เหมาะกับผู้ที่มีพื้นฐานการใช้ฟังก์ชัน INDEX, MATCH และสูตรอาร์เรย์ (Array Formula)
1. INDEX+MATCH
ถ้าลองเขียนสูตรโดยใช้ INDEX + MATCH
{=INDEX(
$C$2:$C$8,
MATCH(2,1/($B$2:$B$8=F2),1)
)}
จะได้ผลลัพธ์ตามต้องการ

สูตรนี้ทำงานยังไง?
ลองวิเคราะห์จาก MATCH ก่อน
จากโครงสร้างของฟังก์ชัน MATCH
MATCH(
lookup_value,
lookup_array,
[match_type]
)
เคสนี้เขียนสูตรเป็น
MATCH(
2,
1/($B$2:$B$8=F2),
1
)
ระบุ lookup_value เป็น 2
ระบุ lookup_array เป็น 1/($B$2:$B$8=F2)
ระบุ [match_type] เป็น 1 เพื่อให้ค้นหาแบบ Approximate Match
ทำไมระบุ lookup_array เป็น 1/($B$2:$B$8=F2)?
ถ้าจะอธิบาย ต้องเริ่มจาก $B$2:$B$8=F2
$B$2:$B$8=F2 คือการเช็คว่าบรรทัดนั้นเป็น F2 (Thailand) หรือเปล่า
ถ้าบรรทัดนั้นเป็น Thailand ผลลัพธ์คือ TRUE
ถ้าบรรทัดนั้นเป็นประเทศอื่น ผลลัพธ์คือ FALSE
ผลลัพธ์ของ $B$2:$B$8=F2 คือ
{TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}
หรือถ้าแสดงเป็นภาพคือ

เมื่อนำ $B$2:$B$8=F2 ไปหารด้วย 1 หรือเขียนสูตรเป็น
1/($B$2:$B$8=F2)
ผลลัพธ์ที่ได้คือ
{1;1;#DIV/0!;1;#DIV/0!;1;#DIV/0!}
นั่นคือบรรทัดที่เป็น Thailand จะกลายเป็น 1
ส่วนบรรทัดที่เป็นประเทศอื่นจะกลายเป็น #DIV/0!
ถ้าอธิบายเป็นภาพคือแบบนี้

“ผมพอเข้าใจล่ะ แต่ทำไมต้องใช้ lookup_value เป็น 2 ทำไมไม่ใช้ 1 ล่ะครับ?” ตั้มถาม
เป็นคำถามที่ดีมาก และนั่นคือทริกสำคัญของสูตรนี้
การระบุ lookup_value เป็น 2 เพื่อให้ได้ค่าสุดท้าย!
(เหตุผลที่ใช้ทริกนี้ได้เพราะระบุการค้นหาเป็นแบบ Approximate Match)
(ในทางตรงข้าม ถ้าระบุการค้นหาเป็นแบบ Exact Match จะใช้ทริกนี้ไม่ได้)
เคสนี้ ผลลัพธ์ใน lookup_array มีแค่ 1 กับ #DIV/0!
ฟังก์ชัน MATCH จะมองข้าม #DIV/0! หรือมองเฉพาะ 1
การใช้ lookup_value เป็น 2 เพื่อให้มั่นใจว่าได้ค่าลำดับสุดท้ายของข้อมูลที่เป็น 1
(เพราะ 2 มากกว่า 1)
ถ้าอธิบายเป็นภาพคือแบบนี้

“งั้นถ้าไม่ใช้ 2 แต่ใช้ 3 แทนได้ไหมครับ?”
“ได้แน่นอน จะเป็น 3, 10, 100, 2500 ได้ทั้งนั้น ขอให้มากกว่า 1 ก็พอ” ผมยิ้ม
MATCH ในสูตรทำงานแบบนี้
=MATCH(
2,
1/($B$2:$B$8=F2),
1
)
=MATCH(
2,
{1;1;#DIV/0!;1;#DIV/0!;1;#DIV/0!},
1
)
=6
ผลลัพธ์ที่ได้คือ 6 และนั่นคือตำแหน่งสุดท้ายของ Thailand
พอได้ตำแหน่งสุดท้ายแล้ว ก็ส่งค่ากลับมาด้วย INDEX
หรือการทำงานของสูตรคือ
{=INDEX(
$C$2:$C$8,
MATCH(2,1/($B$2:$B$8=F2),1)
)}
{=INDEX(
{100;200;300;400;500;600;700},
6
)}
=600
“อย่างนี้นี่เอง เจ๋งไปเลย” ตั้มร้อง
“แต่สูตรนี้มีข้อเสียนะ”
“เอ๋! มีข้อเสียด้วยเหรอครับ?”
ข้อเสียสำคัญคือต้องกด Ctrl+Shift+Enter เพราะสูตรนี้เป็นสูตรอาร์เรย์ (Array Formula)
lookup_array ใน MATCH ไม่รองรับการเขียนสูตรในลักษณะนี้ จึงต้องทำให้เป็นสูตรอาร์เรย์
(วงเล็บปีกกา ( { } )ที่ครอบสูตรไม่ได้เกิดจากการพิมพ์ แต่เกิดจากการกด Ctrl+Shift+Enter)
แต่.. ถ้าใครใช้ Microsoft 365 ไม่ต้องกด Ctrl+Shift+Enter เพราะ Microsoft 365 มีคุณสมบัติที่เรียกว่า Dynamic Array Formula
ทุกสูตรสามารถเป็นสูตรอาร์เรย์โดยกด Enter ธรรมดา
“งั้นมีวิธีที่ไม่ต้องกด Ctrl+Shift+Enter ไหมครับ?” ตั้มถาม
“มีสิ นั่นคือวิธีที่ 2”
2. LOOKUP
ฟังก์ชันที่รองรับสูตรอาร์เรย์โดยไม่ต้องกด Ctrl+Shift+Enter มีเพียง 4 ตัวคือ
- SUMPRODUCT
- AGGREGATE
- INDEX
- LOOKUP
มาลองใช้ LOOKUP กับเคสนี้กัน
หรือเขียนสูตรเป็น
=LOOKUP(
2,
1/($B$2:$B$8=F2),
$C$2:$C$8
)
ได้ผลลัพธ์ตามต้องการ

ทำไมเขียนสูตรแบบนี้?
จากโครงสร้างของ LOOKUP
LOOKUP(
lookup_value,
lookup_vector,
[result_vector]
)
เคสนี้ใช้ lookup_value เป็น 2 เพื่อให้มั่นใจว่าได้ตำแหน่งสุดท้าย (หลักการเดียวกับ MATCH)
ใช้ lookup_vector เป็น 1/($B$2:$B$8=F2)
(หลักการเดียวกับ MATCH เช่นกัน)
ความเจ๋งของ LOOKUP ที่มากกว่า MATCH คือ สามารถส่งค่ากลับมาด้วยตัวมันเอง (โดยไม่ต้องพึ่ง INDEX)
ค่าที่ส่งกลับมาคือ [result_vector] หรือเคสนี้คือ $C$2:$C$8
การค้นหาของ LOOKUP เป็นแบบ Approximate Match อยู่แล้ว (จึงไม่ต้องระบุ) แถมยังไม่ต้องกด Ctrl+Shift+Enter
ในทางปฏิบัติสูตรนี้จึงเป็นที่นิยม
คนเก่ง ๆ หลายคนก็ใช้สูตรแบบนี้
“สูตรนี้มีข้อเสียไหมครับ?” ตั้มถาม
“เรียกว่าไม่มีก็ได้นะ แต่ถ้าจะมีก็มีนิดเดียว นั่นคือสามารถเขียนสูตรให้สั้นกว่านี้ได้อีก”
“สั้นกว่านี้ได้อีกหรือครับ!?”
3. LOOKUP 9^99
จากโครงสร้างของ LOOKUP จะพบว่า [result_vector] มีวงเล็บก้ามปูครอบ
แปลว่าเป็นออปชัน จะใส่หรือไม่ใส่ก็ได้
ถ้าไม่ใส่ [result_vector] โปรแกรมจะส่งค่าใน lookup_vector กลับมาแทน
แต่เคสนี้ระบุ lookup_vector เป็น $C$2:$C$8
หรือเขียนสูตรแบบนี้ไม่ได้
=LOOKUP(2,$C$2:$C$8)
ผลลัพธ์จะกลายเป็น #N/A

ทำไม?
เหตุผลมี 2 ข้อ
1. 2 มีค่าน้อยเกินไป
lookup_value หรือ 2 มีค่าน้อยเกินไป
คำว่า “น้อยเกินไป” หมายความว่า น้อยกว่า $C$2:$C$8
ซึ่งเคสนี้ค่าของ $C$2:$C$8 คือ
100;200;300;400;500;600;700
ผลลัพธ์คือ หา 2 (จากข้อมูลนี้) ไม่เจอ
=LOOKUP(2,$C$2:$C$8)
=LOOKUP(2,{100;200;300;400;500;600;700})
=#N/A
ทางแก้คือ ต้องเปลี่ยน lookup_value ให้มีค่ามากกว่าตัวเลขสูงสุดของ $C$2:$C$8
“งั้นตอนนี้ตัวเลขสูงสุดคือ 700 เราก็เปลี่ยนจาก 2 เป็น 800 ได้ไหมครับ?” ตั้มเอ่ย
“ได้ แต่ถ้าในอนาคตมีข้อมูล 800, 900 สูตรก็จะใช้ไม่ได้” ผมตอบ
ทางแก้คือ ต้องเปลี่ยน lookup_value ให้เป็นตัวเลขมากที่สุดเท่าที่จะมากได้
“ล้านนึงพอไหมพี่?” ตั้มเสนอ
“ถ้ามั่นใจว่าตัวเลขไม่มีโอกาสเกินล้าน ใช้ล้านนึงได้เลย” ผมยิ้ม
ถ้าให้ชัวร์ ลองใช้เป็นตัวเลขที่เยอะแบบเวอร์ ๆ เช่น 9^99
9^99 คือ 9 ยกกำลัง 99
มีค่าเท่ากับ 2.95127E+94

นึกภาพง่าย ๆ เลข 2 แล้วมีเลขศูนย์ต่อท้าย 94 ตัว!
แบบนี้เยอะพอแน่นอน ^_^
2. ยังไม่ได้ระบุเงื่อนไขในสูตร
ถ้าเขียนสูตรเป็น
=LOOKUP(9^99,$C$2:$C$8)
คำตอบก็ยังคงผิด

ทำไม?
เพราะจะได้ผลลัพธ์เป็นค่าสุดท้ายของข้อมูล
แต่เคสนี้ ค่าสุดท้ายของข้อมูลไม่ตรงกับเงื่อนไข (Thailand)
งั้นก็ต้องทำให้ lookup_vector แสดงเฉพาะค่าที่ตรงกับเงื่อนไข
ทำยังไง?
เทคนิคที่ใช้คือการนำเงื่อนไขไปหาร
ทำไมต้องนำเงื่อนไขไปหาร?
เพื่อทำให้บรรทัดที่ไม่ตรงกับเงื่อนไขกลายเป็น #DIV/0!
เคสนี้เงื่อนไขคือ $B$2:$B$8=F2
ดังนั้นจึงเปลี่ยน lookup_vector จาก
$C$2:$C$8
เป็น
$C$2:$C$8/($B$2:$B$8=F2)
ผลลัพธ์จาก lookup_vector คือ
=$C$2:$C$8/($B$2:$B$8=F2)
={100;200;300;400;500;600;700}/
{TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}
={100;200;#DIV/0!;400;#DIV/0!;600;#DIV/0!}
หรือเขียนสูตรเต็ม ๆ เป็น
=LOOKUP(
9^99,
$C$2:$C$8/($B$2:$B$8=F2)
)
ได้คำตอบตามต้องการ

อธิบายเป็นภาพได้แบบนี้

LOOKUP จะมองข้ามบรรทัดที่เป็น #DIV/0!
คำตอบที่ได้จึงเป็นบรรทัดสุดท้ายที่ไม่ใช่ #DIV/0!
เคสนี้คือ 600
“เทคนิคลึกล้ำมากเลยครับ แถมสูตรสั้นด้วย” ตั้มโพล่งขึ้น
“สูตรนี้สั้นจริง แต่มีข้อเสียนิดหน่อยนะ”
ข้อเสียของสูตรนี้คือ lookup_vector ต้องเป็นตัวเลขเท่านั้น
เคสนี้ใช้สูตรนี้ได้ เพราะ $C$2:$C$8 เป็นตัวเลข
แต่ถ้าเป็นตัวหนังสือ (Text) แบบนี้

ก็จะใช้ไม่ได้
“ตั้มใช้ Microsoft 365 หรือเปล่า?” ผมถาม
“ใช้ครับ”
“งั้นพี่แนะนำให้ใช้ XLOOKUP” ผมยิ้ม
4. XLOOKUP
โครงสร้างของฟังก์ชัน XLOOKUP คือ
XLOOKUP(
lookup_value,
lookup_array,
return_array,
[if_not_found],
[match_mode],
[search_mode]
)
lookup_value, lookup_array นิยามเหมือนกับอาร์กิวเมนต์ (Argument) ของฟังก์ชัน MATCH
return_array คือ ช่วงข้อมูลที่ต้องการ
(คล้ายกับ [result_vector] ของฟังก์ชัน LOOKUP)
[if_not_found] แปลว่า ถ้าหาไม่เจอจะให้แสดงผลยังไง
ถ้าไม่ระบุ เวลาหาไม่เจอจะแสดงผลเป็น #N/A (ค่าดีฟอลต์)
ซึ่งเคสนี้เราจะไม่ระบุ
[match_mode] คือรูปแบบการ match ซึ่งมี 4 แบบคือ
0: Exact match (ค่าดีฟอลต์)
-1: Exact match or next smaller than
1: Exact match or next larger than
2: Wildcard character match
เคสนี้เราจะระบุเป็น 0 แปลว่าต้องเป็นตัวนั้นเป๊ะ ๆ
ความเจ๋งมันอยู่ตรง [search_mode] คือ รูปแบบการค้นหา มี 4 แบบคือ
1: Search first-to-last
-1: Search last-to-first
2: Binary search (sorted ascending order)
-2: Binary search (sorted descending order)
เคสของเราข้อมูลไม่ได้ถูกเรียง ดังนั้นระบุได้เพียง 2 แบบคือ 1, -1
และคำตอบที่ต้องการคือ ข้อมูลตัวสุดท้าย จึงระบุเป็น -1
หรือเขียนสูตรเต็ม ๆ เป็น
=XLOOKUP(
F2,
$B$2:$B$8,
$C$2:$C$8,
,
0,
-1
)
จบเลย!

ง่ายสุด ๆ ไม่ต้องจับไปหารนู่นหารนี่ให้วุ่นวาย ^_^
ข้อเสียมีเพียงอย่างเดียวคือต้องใช้ Microsoft 365 เท่านั้น
สรุป
ถ้าไม่ได้ใช้ Microsoft 365 ใช้เทคนิคแบบที่ 2 ( LOOKUP(2,,) )
หรือแบบที่ 3 ( LOOKUP(9^99,) )
แต่แบบที่ 3 ใช้ได้กรณีข้อมูลที่ต้องการเป็นตัวเลขเท่านั้น
ถ้าใช้ Microsoft 365 ใช้เทคนิคแบบที่ 4 (XLOOKUP) ง่ายสุด ^_^
เป็นยังไงกันบ้างครับ
บทความนี้อธิบายแนวคิดและสเต็ปการคำนวณอย่างละเอียด นอกจากใช้กับเคสดึงค่าสุดท้ายได้แล้ว ยังสามารถนำไปประยุกต์กับเคสอื่นได้ด้วย
หวังว่าจะเป็นประโยชน์กับทุกคนนะครับ ^_^
สำหรับใครที่สนใจไฟล์ตัวอย่างของบทความนี้ ดาวน์โหลดเพื่อศึกษาเพิ่มเติมได้เลยครับ
Special Thanks: Excel Wizard
Reference:
https://exceljet.net/how-to-lookup-first-and-last-match
https://www.facebook.com/XcWizard/posts/2338868149514947
หาวันที่ที่จำนวนเริ่มติดลบครับ มีสูตรแบบไหนบ้างครับ