“อยากดึงข้อมูลตัวสุดท้าย ต้องเขียนสูตรยังไงครับ?” ตั้มโพล่งขึ้น
“ใช้ 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 ตัวคือ

1. SUMPRODUCT
2. AGGREGATE
3. INDEX
4. 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