8 เทคนิค Excel ดึงข้อมูลแบบหลายเงื่อนไข [Multiple Criteria Lookup]

8 เทคนิค Excel ดึงข้อมูลแบบหลายเงื่อนไข [Multiple Criteria Lookup]

February 7, 2021August 11, 2021 by วิศวกรรีพอร์ต

“VLOOKUP ดึงข้อมูลแบบ 2 เงื่อนไขได้ไหมครับ?”

“หือม์.. ยังไงนะตั้ม?” คำถามตั้มทำผมงง
“คือผมมีชื่อประเทศ กับขนาดของสาขา อยากเขียนสูตรเพื่อดึงชื่อสาขานั้นครับ”

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

สิ่งที่ต้องการคือ ถ้ามีเงื่อนไขเป็นชื่อประเทศ (F4) กับขนาดของสาขา (G4) ต้องเขียนสูตรยังไงถึงจะได้ชื่อสาขานั้น (H4)

“ตอนนี้ผมใช้วิธีสร้างคอลัมน์เพิ่มแล้วจับมาเชื่อมกัน จากนั้นค่อยใช้ VLOOKUP” ตั้มอธิบาย

วิธีที่ตั้มใช้คือ สร้างคอลัมน์เพิ่มโดยแทรก (Insert) คอลัมน์ A ด้วยสูตร B4&C4

จากนั้นใช้สูตร

=VLOOKUP(G4&H4,$A$4:$D$12,4,0)

ได้คำตอบตามต้องการ

“แต่ผมไม่ชอบวิธีนี้ เพราะต้องแทรกคอลัมน์ที่ตำแหน่งซ้ายสุด บางครั้งทำแบบนั้นไม่ได้”
“ใช่ วิธีนี้ไม่เวิร์ก” ผมเห็นด้วย
“พี่พอมีวิธีอื่นไหมครับ?”
“มีสิ แถมมี 8 วิธีเลยนะ” ผมยิ้ม

หมายเหตุ: บทความนี้มีเนื้อหาประยุกต์ เหมาะกับผู้ที่มีพื้นฐานการใช้ฟังก์ชัน INDEX, MATCH และสูตรอาร์เรย์ (Array Formula)

1. VLOOKUP + CHOOSE

เคสนี้จะเขียนสูตรง่ายมาก ถ้ามีตารางหน้าตาแบบนี้

ในเมื่อไม่มี งั้นก็สร้างตารางนี้ขึ้นมาเอง ด้วยสูตร

CHOOSE(
   {1,2},
   $A$4:$A$12&$B$4:$B$12,
   $C$4:$C$12
)

ผลลัพธ์ก็คือตารางหน้าตาแบบเมื่อกี๊นั่นเอง ผมขอเรียกเป็น ‘ตารางเสมือน’ ละกัน ^_^

จากโครงสร้างของ VLOOKUP

VLOOKUP(
   lookup_value,
   table_array,
   column_index_num,
   [range_lookup]
)

ให้มองว่าสูตรเมื่อกี้คือ table_array ของ VLOOKUP
หรือจับ CHOOSE ยัดเข้าไปใน VLOOKUP เป็น

{=VLOOKUP(
    F4&G4,
    CHOOSE(
       {1,2},
       $A$4:$A$12&$B$4:$B$12,
       $C$4:$C$12
    ),
    2,
    0
)}

ได้คำตอบตามต้องการ

แต่วิธีนี้มีข้อเสียหลายข้อ

ข้อเสีย

คำนวณช้า
เพราะการสร้างตารางเสมือนด้วยสูตร CHOOSE({1,2},$A$4:$A$12&$B$4:$B$12,$C$4:$C$12) กินพลังในการประมวลผล
ความยาวของ lookup_value ห้ามเกิน 255 อักขระ
ในที่นี้ lookup_value คือ F4&G4 (ChinaMedium)
แปลว่าความยาวของเงื่อนไขทั้งหมดห้ามเกิน 255 อักขระ

ถ้ามีแค่ 2 เงื่อนไข ไม่มีปัญหา
แต่ถ้ามี 4 เงื่อนไขล่ะ?
lookup_value จะกลายเป็น
F4&G4&H4&I4
แบบนี้มีโอกาสเกิน 255 อักขระ

3. ต้องกด Ctrl+Shift+Enter
วงเล็บปีกกาในสูตร ( { } ) ไม่ได้เกิดจากการพิมพ์ แต่เกิดจากการกด Ctrl+Shift+Enter เพราะสูตรนี้เป็นสูตรอาร์เรย์ (Array Formula)
แปลว่าพอพิมพ์สูตรเสร็จ แทนที่จะกด Enter ต้องเปลี่ยนเป็นกด Ctrl+Shift+Enter ทำให้เกิดความไม่สะดวก

อ้อ! ถ้าใครใช้ Microsoft 365 ไม่ต้องกด Ctrl+Shift+Enter นะครับ กด Enter แบบปกติได้เลย
เพราะ Microsoft 365 มีคุณบัติที่เรียกว่า Dynamic Array Formula
ทุกสูตรสามารถเป็นสูตรอาร์เรย์โดยกด Enter แบบปกติ

แม้สูตรไม่แสดงวงเล็บปีกกา แต่ได้ผลลัพธ์เหมือนกัน

ด้วยข้อเสีย 3 ข้อ ในทางปฏิบัติสูตรนี้จึงไม่ค่อยได้รับความนิยม

2. INDEX + MATCH

เคสนี้ถ้ามีคอลัมน์ใดแสดงข้อมูลแบบนี้

เราก็ใช้ MATCH เพื่อหาตำแหน่ง แล้วแสดงผลลัพธ์ด้วย INDEX ได้

ในเมื่อไม่มี เราก็สร้างเองด้วยสูตร

$A$4:$A$12&$B$4:$B$12

ผมขอเรียกสูตรนี้ว่าเป็นการสร้าง ‘คอลัมน์เสมือน’ ละกัน ^_^

จากโครงสร้างของฟังก์ชัน MATCH

MATCH(
   lookup_value,
   lookup_array,
   [match_type]
)

ให้มอง $A$4:$A$12&$B$4:$B$12 เป็น lookup_array
แล้วเปลี่ยนวิธีเขียน MATCH เป็น

MATCH(
   F4&G4,
   $A$4:$A$12&$B$4:$B$12,
   0
)

จากนั้นดึงข้อมูลโดยใช้ INDEX
หรือเขียนสูตรเต็ม ๆ เป็น

{=INDEX(
    $C$4:$C$12,
    MATCH(
       F4&G4,
       $A$4:$A$12&$B$4:$B$12,
       0
    )
)}

ได้ผลลัพธ์ตามต้องการ

แต่วิธีนี้ก็มีข้อเสียเช่นกัน

ข้อเสีย

คำนวณช้า
เพราะการสร้างคอลัมน์เสมือนด้วยสูตร $A$4:$A$12&$B$4:$B$12 กินพลังในการประมวลผล
ความยาวของ lookup_value ห้ามเกิน 255 อักขระ
(ฟังก์ชัน MATCH ก็มี lookup_value และติดข้อจำกัดเดียวกัน)
ต้องกด Ctrl+Shift+Enter (ยกเว้น Microsoft 365)

“ดูแล้วข้อเสียเหมือนสูตรแรกเลยนะครับ” ตั้มเอ่ย
“ใช่แล้ว ในทางปฏิบัติ หลายคนหลีกเลี่ยงใช้สูตรนี้” ผมตอบ
“มีสูตรที่ไม่ติดปัญหาเรื่อง lookup_value ห้ามเกิน 255 อักขระ ไหมครับ?” ตั้มถาม
“มีสิ นั่นคือวิธีที่ 3”

3. INDEX + MATCH + Logic

ปัญหาของสูตรที่ 2 (และสูตรที่ 1) คือ ถ้าเขียนสูตรโดยใช้ MATCH แบบนี้

MATCH(
   F4&G4,
   $A$4:$A$12&$B$4:$B$12,
   0
)

ยังไงก็เจอปัญหาเรื่อง lookup_value ห้ามเกิน 255 อักขระ
ทางแก้คือต้องเปลี่ยนวิธีการเขียน MATCH
โดยเปลี่ยนเป็นแบบนี้แทน

MATCH(
   1,
   ($A$4:$A$12=F4)*($B$4:$B$12=G4),
   0
)

หรือสูตรเต็ม ๆ ที่ใช้ร่วมกับ INDEX คือ

INDEX(
  $C$4:$C$12,
  MATCH(
    1,
    ($A$4:$A$12=F4)*($B$4:$B$12=G4),
    0
  )
)

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

ลองดู lookup_array ของ MATCH

($A$4:$A$12=F4)*($B$4:$B$12=G4)

ผลลัพธ์จะเป็น TRUE, FALSE แบบนี้

{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*
{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}

พอคูณกันแล้ว ผลลัพธ์คือ

{0;1;0;0;0;0;0;0;0}

แปลว่า MATCH กลายเป็น

=MATCH( 1, {0;1;0;0;0;0;0;0;0}, 0 )

=2

ผลลัพธ์ก็คือ 2
พูดง่าย ๆ คือการเขียน MATCH แบบนี้ เป็นการหาว่าบรรทัดใดที่ทำให้ทุกเงื่อนไขเป็นจริงนั่นเอง!
(TRUE เทียบเท่า 1, FALSE เทียบเท่า 0)

พอนำไปรวมกับ INDEX กลายเป็น

=INDEX($C$4:$C$12,2)

=INDEX({"Store01";"Store02";"Store03";"Store04";"Store05";"Store06";"Store07";"Store08";"Store09"},2)

="Store02"

ได้ผลลัพธ์เป็น Store02

เทคนิคนี้ใช้ lookup_value เป็น 1 ยังไงก็ไม่ติดปัญหาเรื่องห้ามเกิน 255 อักขระ

แต่ก็ยังมีข้อเสีย

นั่นคือต้องกด Ctrl+Shift+Enter (ยกเว้น Microsoft 365)

“มีวิธีที่ไม่ต้องกด Ctrl+Shift+Enter ไหมครับ?” ตั้มถาม
“มีสิ นั่นคือวิธีที่ 4”

4. INDEX + MATCH + INDEX

ปัญหานึงของเทคนิคที่ 2,3 คือ lookup_array ของ MATCH เป็นสูตรอาร์เรย์ ยังไงก็ต้องกด Ctrl+Shift+Enter

ถ้าไม่อยากกด Ctrl+Shift+Enter ต้องเปลี่ยนวิธีเขียนสูตร โดยใช้ร่วมกับฟังก์ชันที่รองรับสูตรอาร์เรย์

ฟังก์ชันที่รองรับสูตรอาร์เรย์แบบไม่ต้องกด Ctrl+Shift+Enter มีเพียง 4 ฟังก์ชันคือ

SUMPRODUCT
AGGREGATE
LOOKUP
INDEX

ในที่นี้เราจะใช้ INDEX ช่วย

นั่นคือเปลี่ยนวิธีเขียน lookup_array จาก

($A$4:$A$12=F4)*($B$4:$B$12=G4)

เป็น

INDEX(
   ($A$4:$A$12=F4)*($B$4:$B$12=G4),
   0
)

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

จากโครงสร้างของ INDEX

INDEX(
   array,
   row_num,
   [column_num]
)

การระบุ row_num เป็น 0 คือเทคนิคที่ทำให้แสดงผลลัพธ์ทุกบรรทัด
ผลลัพธ์จากสูตรนี้คือ

=INDEX(
    ($A$4:$A$12=F4)*($B$4:$B$12=G4),
    0
 )

={0;1;0;0;0;0;0;0;0}

จะพบว่าผลลัพธ์เหมือนกับ ($A$4:$A$12=F4)*($B$4:$B$12=G4) แต่ข้อดีคือไม่ต้องกด Ctrl+Shift+Enter นั่นเอง

ดังนั้นวิธีการเขียน MATCH จะเปลี่ยนเป็น

MATCH(
  1,
  INDEX(
    ($A$4:$A$12=F4)*($B$4:$B$12=G4),
    0
  ),
  0
)

หรือสูตรเต็ม ๆ เมื่อใช้กับ INDEX ก็คือ

INDEX(
  $C$4:$C$12,
  MATCH(
   1,
   INDEX(
    ($A$4:$A$12=F4)*($B$4:$B$12=G4),
    0
   ),
   0
  )
)

ได้ผลลัพธ์ตามต้องการ

สูตรนี้คนเก่ง ๆ มักชอบใช้กัน เพราะแก้ปัญหาเรื่อง lookup_value ห้ามเกิน 255 อักขระ และไม่ต้องกด Ctrl+Shift+Enter

“งั้นสูตรนี้ดีที่สุดแล้วใช่ไหมครับ?” ตั้มถาม
“สูตรนี้ถือว่าโอเคเลย แต่มีข้อเสียนิดนึงที่อาจคำนวณไม่เร็วนัก”
“ทำไมถึงคำนวณไม่เร็วล่ะครับ?”
“เพราะการเขียน MATCH แบบนี้ใช้การค้นหาแบบ Exact Match ถ้าเจอข้อมูลเยอะ ๆ จะทำงานช้า” ผมตอบ
“งั้นมีสูตรที่ทำงานเร็วกว่านี้ไหมครับ?”
“มีสิ นั่นคือ เทคนิคที่ 5”

5. LOOKUP

ถ้าต้องการให้สูตรทำงานเร็ว อาจเปลี่ยนการค้นหาจาก Exact Match เป็น Approximate Match (Binary Search)
ซึ่งหนึ่งในฟังก์ชันที่ใช้หลักการค้นหาแบบ Approximate Match ก็คือ LOOKUP นั่นเอง

Exact Match, Approximate Match คืออะไร?
คือหลักการค้นหาข้อมูล ใช้ในหลายฟังก์ชัน เช่น VLOOKUKP, MATCH, LOOKUP

ดูหลักการทำงานของ Exact Match, Approximate Match ได้จากคลิปของคุณ Excel Wizard
คลิปนี้บอกเลยว่าดีมาก ๆ ไม่มีคลิปไหนอธิบายได้เห็นภาพและละเอียดเท่านี้อีกแล้ว ^_^

โดยเขียนสูตรเป็น

LOOKUP(
  1,
  1/($A$4:$A$12=F4)/($B$4:$B$12=G4),
  $C$4:$C$12
)

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

จากโครงสร้างของ LOOKUP

LOOKUP( 
    lookup_value, 
    lookup_vector, 
    [result_vector]
)

แปลว่าใช้ 1/($A$4:$A$12=F4)/($B$4:$B$12=G4) เป็น lookup_vector

ทำไมต้องเขียนเป็น 1/($A$4:$A$12=F4)/($B$4:$B$12=G4) ?

เพราะเป็นเทคนิคที่ใช้ข้ามข้อมูลที่เป็น error

=1/($A$4:$A$12=F4)/($B$4:$B$12=G4)

=1/{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}/{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}

={#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}

แปลว่าบรรทัดที่ไม่ตรงกับเงื่อนไขจะถูกเปลี่ยนเป็น #DIV/0!

เคสนี้ บรรทัดที่ตรงกับทุกเงื่อนไขก็คือบรรทัดที่ 2 (สังเกตว่าผลลัพธ์ตัวที่ 2 คือ 1)

เมื่อนำมาใช้กับ LOOKUP จะถูกคำนวณในลักษณะนี้

=LOOKUP(1,
1/($A$4:$A$12=F4)/($B$4:$B$12=G4),
$C$4:$C$12)

=LOOKUP(1,
{#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!},
$C$4:$C$12)

=LOOKUP(1,
{#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!},
{"Store01";"Store02";"Store03";"Store04";"Store05";"Store06";"Store07";"Store08";"Store09"})

="Store02"

ถ้าอธิบายเป็นภาพ สูตรทำงานแบบนี้

ด้วยความที่ LOOKUP เป็น 1 ใน 4 ฟังก์ชันที่ใช้สูตรอาร์เรย์ได้เลย สูตรนี้จึงไม่ต้องกด Ctrl+Shift+Enter

“งั้นสูตรนี้ก็ไม่มีข้อเสียเลยใช่ไหมครับ?” ตั้มโพล่งขึ้น
“ใช่ ถ้าไม่มีข้อมูลซ้ำนะ” ผมตอบ

ถ้ามีข้อมูลซ้ำ และข้อมูลตารางต้นทางไม่ได้ถูกเรียงลำดับจากน้อยไปหามาก (เรียงตามตัวเลขหรือตัวอักษร) ผลลัพธ์จากสูตรนี้จะคาดเดาไม่ได้

จากภาพ ถ้าข้อมูลบรรทัดที่ 12 ถูกเปลี่ยนเป็น China, Medium
จะพบว่าผลลัพธ์ของสูตรนี้เปลี่ยนจาก Store02 เป็น Store09

แต่ถ้ามีข้อมูลซ้ำเพิ่มเติมในบรรทัดที่ 8 ผลลัพธ์จะเปลี่ยนเป็น Store05

เมื่อเทียบกับการเขียนสูตรแบบที่ 1-4 ถ้ามีข้อมูลซ้ำ จะได้ค่าแรกเสมอ

เพราะการเขียนสูตรแบบที่ 1-4 เป็นการค้นหาข้อมูลแบบ Exact Match ถ้ามีข้อมูลซ้ำ จะได้ค่าแรกเสมอ

แต่ถ้ามีข้อมูลซ้ำ แล้วต้องการได้ผลลัพธ์เป็นค่าสุดท้าย ให้ใช้ LOOKUP แล้วปรับ lookup_value จาก 1 เป็น 2

หรือเขียนสูตรเป็น

=LOOKUP(2,
  1/($A$4:$A$12=F4)/($B$4:$B$12=G4),
  $C$4:$C$12
 )

จะได้ผลลัพธ์เป็นค่าสุดท้ายเสมอ

ทำไมถึงเป็นเช่นนั้น?

เพราะสูตรจะหาว่า 2 อยู่ตำแหน่งใดใน 1/($A$4:$A$12=F4)/($B$4:$B$12=G4)

=1/($A$4:$A$12=F4)/($B$4:$B$12=G4)

={#DIV/0!;1;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;1}

ซึ่งแน่นอนว่าข้อมูลชุดนี้ไม่มี 2 (มีแต่ 1, #DIV/0!)
สูตรจะข้ามข้อมูลที่เป็น #DIV/0! แล้วหยุดตรงตำแหน่งของ 1 ตัวสุดท้าย จากนั้นดึงค่ากลับมานั่นเอง

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

ในทางปฏิบัติ คนเก่ง ๆ หลายคนชอบใช้สูตรนี้ (ระบุ lookup_value เป็น 2)
เพราะถ้าไม่มีข้อมูลซ้ำ ก็ได้ค่าที่ต้องการ
ถ้ามีข้อมูลซ้ำ ก็ได้ค่าสุดท้าย

อย่างไรก็ตาม แม้ LOOKUP จะเป็นการค้นหาแบบ Approximate Match แต่โครงสร้างสูตรมีการหาร ( 1/($A$4:$A$12=F4)/($B$4:$B$12=G4) ) จึงทำให้ความเร็วในการประมวลผลไม่เร็วกว่าแบบที่ 3 หรือ 4

ถ้าพูดในแง่ความเร็วแล้ว สูตรที่น่าจะประมวลผลเร็วที่สุดคือ DGET

6. DGET

โครงสร้างของ DGET คือ

DGET( database, field, criteria )

เคสนี้ถ้าเขียนสูตรเป็น

=DGET(A3:C12,3,F3:G4)

จะได้ผลลัพธ์ตามต้องการ

“เฮ้ยพี่! สูตรสั้นมากอ่ะ” ตั้มร้องเสียงดัง
“ใช้ สูตรสั้น แถมทำงานเร็วมาก แต่ในทางปฏิบัติ DGET เป็นวิธีที่ไม่นิยมเลย” ผมตอบ
“ทำไมล่ะครับ?”

เพราะ DGET เป็นฟังก์ชันตระกูล Database
ข้อเสียของฟังก์ชันตระกูล Database คือ ต้องระบุ criteria แบบมีหัวคอลัมน์เสมอ

เช่น ต้องระบุ criteria เป็น F3:G4
หรือเป็นในรูปแบบนี้เสมอ

ถ้าต้องการดึงข้อมูลอีกครั้ง ต้องระบุเงื่อนไขติดกับชื่อหัวคอลัมน์ตลอด

จะเขียนติดกัน แล้วลากสูตร (copy สูตร) ไม่ได้

ด้วยเหตุผลที่ไม่สามารถลากสูตร (copy สูตร) แม้สูตรเขียนง่ายและคำนวณเร็ว แต่ในทางปฏิบัติ DGET และฟังก์ชันตระกูล Database (เช่น DSUM, DCOUNT) จึงไม่เป็นที่นิยมเลย

7. XLOOKUP

“ตั้มใช้ Microsoft 365 หรือเปล่า?” ผมถาม
“ใช้ครับ ทำไมหรือครับ”
“ทำไมไม่ลองใช้ฟังก์ชัน XLOOKUP ล่ะ” ผมยิ้ม

โครงสร้างของฟังก์ชัน XLOOKUP คือ

XLOOKUP(
   lookup_value,
   lookup_array,
   return_array,
   [if_not_found],
   [match_mode],
   [search_mode]
)

จะพบว่า argument ที่ 4-6 มีวงเล็บก้ามปู ( [ ] ) ครอบ แปลว่าเป็น option ไม่ต้องระบุก็ได้

เพื่อให้เข้าใจง่าย ดูแค่ 3 argument แรกก็พอ นั่นคือ

XLOOKUP(
   lookup_value,
   lookup_array,
   return_array
)

จะพบว่าโครงสร้างคล้าย LOOKUP
ดังนั้นเคสนี้ เราก็เขียนสูตรเป็น

=XLOOKUP(
   1,
   ($A$4:$A$12=F4)*($B$4:$B$12=G4),
   $C$4:$C$12
 )

ได้ผลลัพธ์ตามต้องการ

XLOOKUP เป็นฟังก์ชันที่มีใน Microsoft 365 เท่านั้น

ด้วยความที่ใช้ Microsoft 365 จึงไม่ต้องกด Ctrl+Shift+Enter

“ดูแล้วคล้ายกับ LOOKUP มากเลย แบบนี้ใช้ LOOKUP ไม่ดีกว่าหรือครับ?” ตั้มถาม
“ใช้ XLOOKUP ดีกว่า เพราะมีความสามารถที่ LOOKUP ทำไม่ได้”

นั่นคือสามารถระบุ return_array ได้มากกว่า 1 คอลัมน์
ถ้าระบุ 2 คอลัมน์ จะได้ผลลัพธ์ 2 ค่าโดยอัตโนมัติ!

เพื่อให้เห็นภาพ สมมติข้อมูลต้นทางมีคอลัมน์ SalesRep เพิ่มในคอลัมน์ D

ถ้าเขียนสูตรเป็น

=XLOOKUP(
   1,
   ($A$4:$A$12=F4)*($B$4:$B$12=G4),
   $C$4:$D$12
 )

จะพบว่า เขียนสูตรเซลล์เดียว (H4) แต่ได้ผลลัพธ์ 2 เซลล์ (H4, I4) เพราะคุณสมบัติเรื่อง SPILL นั่นเอง

การเขียน XLOOKUP แบบนี้ ถ้ามีข้อมูลซ้ำ จะได้ข้อมูลบรรทัดบนสุด (เหมือนเทคนิคที่ 1-4)

“แล้วถ้าอยากได้ทุกบรรทัดล่ะ?”
คำตอบคือฟังก์ชัน FILTER

8. FILTER

โครงสร้างของฟังก์ชัน FILTER คือ

FILTER(
   array, 
   include, 
   [if_empty]
)

เคสนี้เขียนสูตรเป็น

=FILTER(
   $C$4:$C$12,
   ($A$4:$A$12=F4)*($B$4:$B$12=G4)
)

ได้ผลลัพธ์ตามต้องการ!

ถ้าข้อมูลต้นทางมีคอลัมน์ SalesRep เพิ่มในคอลัมน์ D

แล้วระบุ array เป็น 2 คอลัมน์
เช่น เปลี่ยนจาก $C$4:$C$12 เป็น $C$4:$D$12

=FILTER(
   $C$4:$D$12,
   ($A$4:$A$12=F4)*($B$4:$B$12=G4)
)

ก็ได้ผลลัพธ์ 2 ค่าเหมือน XLOOKUP

FILTER ก็มีคุณสมบัติเรื่อง SPILL เช่นกัน

ที่มันเจ๋งมากคือ ถ้าต้นทางมีข้อมูลซ้ำ
จะได้ข้อมูลที่ตรงกับเงื่อนไขนั้นทุกบรรทัด!

เช่น เคสนี้ได้ผลลัพธ์ 3 บรรทัดโดยอัตโนมัติ

(จริง ๆ แล้วเคสนี้ไม่ต้องกด F4 เพื่อล็อกเซลล์ก็ได้ แต่เหตุผลที่กด F4 เพราะเผื่อกรณีก็อปปี้สูตร (ลากสูตร) ในอนาคต)

“เฮ้ยพี่! FILTER เจ๋งขนาดนี้เลยเหรอ” ตั้มตะโกนดังลั่น
“ใช่เลย FILTER เจ๋งมาก ๆ”

ยิ่งถ้าใช้คู่กับฟังก์ชัน SORT, SORTBY หรือใช้ซ้อนกับ FILTER อีกชั้น เรียกได้ว่าตอบโจทย์แทบทุกรูปแบบ
เป็นหนึ่งในสุดยอดฟังก์ชันเลย ^_^

ข้อเสียน่าจะมีเพียงข้อเดียวคือ ต้องใช้ Microsoft 365 เท่านั้น

สรุป

ถ้าไม่ได้ใช้ Microsoft 365 และไม่มีข้อมูลซ้ำ ใช้ LOOKUP (เทคนิคที่ 5)

ถ้าไม่ได้ใช้ Microsoft 365 แต่มีข้อมูลซ้ำ และต้องการให้แสดงข้อมูลซ้ำเฉพาะบรรทัดแรก ใช้ INDEX + MATCH + INDEX (เทคนิคที่ 4)
(ถ้าต้องการให้แสดงข้อมูลซ้ำทุกบรรทัด ต้องใช้ INDEX + MATCH + SMALL + ROW ซึ่งค่อนข้างซับซ้อน ขอไม่กล่าวถึงในบทความนี้)

ถ้าใช้ Microsoft 365 และไม่มีข้อมูลซ้ำ ใช้ XLOOKUP (เทคนิคที่ 7) หรือ FILTER (เทคนิคที่ 8) ก็ได้
(จริง ๆ แล้ว XLOOKUP มีความสามารถบางอย่างที่ FILTER ไม่มี เช่น ใช้ร่วมกับ wildcard, ค้นหาแบบ binary search แต่ขอไม่กล่าวถึงในบทความนี้)

ถ้าใช้ Microsoft 365 และมีข้อมูลซ้ำ ใช้ FILTER (เทคนิคที่ 8) ง่ายที่สุด

เป็นยังไงกันบ้าง บทความนี้แม้ค่อนข้างยาวแต่ให้การประยุกต์ในหลาย ๆ มุมมอง หวังว่าจะเป็นประโยชน์กับทุกคนนะครับ ^_^

สำหรับใครที่สนใจไฟล์ตัวอย่างของบทความนี้ ดาวน์โหลดเพื่อศึกษาเพิ่มเติมได้เลยครับ

ShowData_MultipleCriteria_210211 Download

Special Thanks: Excel Wizard

Reference:
https://www.ablebits.com/office-addins-blog/2019/12/11/excel-index-match-multiple-criteria-formula-examples/
https://exceljet.net/formula/index-and-match-with-multiple-criteria
https://www.youtube.com/watch?v=WO6Kjba5EPc
https://www.youtube.com/watch?v=yFH01fuVyGs
https://www.youtube.com/watch?fbclid=IwAR3eTZ2Gwz2z876AkZ52QgDuJXcdGCQWw-XOt6XIWhHH81gbJqGaUekm6pA&v=PDJ_bh-8Iz8&feature=youtu.be

วิศวกรรีพอร์ต

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