สารพัดเทคนิคสำหรับคนอยาก VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว

จะทำยังไงถึงจะทำ VLOOKUP ผลลัพธ์หลายค่า จาก Lookup_Value เดียวได้นะ?? นี่คือคำถามที่เกิดขึ้นบ่อย เพราะปกติแล้ว เวลาเราใช้ VLOOKUP หรือจะ INDEX MATCH ก็แล้วแต่ หาก Lookup_Value ในตารางอ้างอิงมันซ้ำกันหลายตัว กรณีแบบ Exact Match มันจะได้ผลลัพธ์เป็นตัวบนสุดตัวเดียวเสมอ…

บทความนี้ผมจะมาบอกวิธีเพื่อแสดงผลลัพธ์หลายบรรทัดจากคำค้นหาเดียวให้เอง และจะทำให้ดูหลายแบบด้วยครับ!! ซึ่งมี VLOOKUP อยู่แค่แบบเดียวเท่านั้น นอกนั้นผมใช้วิธีอื่นหมดเลย 555 (ก็จริงๆ แล้ว VLOOKUP มันไม่ได้เหมาะกับเคสแบบนี้นี่นา)

โจทย์คือแบบนี้ครับ เราต้องการเลือกสินค้า แล้วให้แสดง Order ID ทั้งหมดของสินค้านั้นออกมา (ถ้าเราเอา Order ID ออกมาได้แล้ว อย่างอื่นก็คงไม่ยากแล้วจริงมั้ยครับ?)

บอกไว้ก่อนว่า วิธีกลุ่มที่ 1 ซึ่งคือการเขียนสูตรนั้นยากกว่ากลุ่มที่ 2 ที่ใช้เครื่องมือพอสมควรนะครับ ใครเน้นง่ายก็ไปดูวิธีกลุ่มการใช้เครื่องมือได้เลย แต่ถ้าใครอยากรู้วิธีเขียนสูตรก็อ่านต่อไปได้เลย

กลุ่ม 1 : ใช้การเขียนสูตรเพื่อให้เกิดผลลัพธ์หลายค่า

1.1 ใช้ FILTER (ต้องมี Excel 365 หรือ Excel for web )

ถ้าเรามี Excel365 วิธีที่ง่ายสุดๆ สำหรับการเขียนสูตรคือใช้ฟังก์ชัน FILTER นั่นเองครับ ไม่ต้องไปใช้ VLOOKUP นะ (ก็ VLOOKUP มันได้ทีละค่าไง…)

=FILTER(array,include,[if_empty])
=FILTER(พื้นที่ผลลัพธ์,เงื่อนไขที่ต้องการ,[ถ้าไม่มีผลลัพธ์เลยให้แสดงอะไร])

ตรงเงื่อนไขที่ต้องการ เราใส่เป็น TRUE/FALSE หรือ 1,0 ก็ได้ นะครับ และไม่จำเป็นต้องอยู่ใน array ผลลัพธ์ก็ได้นะครับ

เช่น ผมต้องการ order_id ที่สินค้าเป็นแอปเปิ้ล ผมก็เขียนแบบนี้ได้เลย ใน H6

=FILTER(B3:B14,C3:C14=G3)

จะเห็นว่าเงื่อนไข C3:C14 ไม่ได้อยู่ใน B3:B14 นะครับ แค่ต้องจับคู่กันให้ได้เท่านั้นเอง

สังเกตว่าผลลัพธ์จะงอกออกมาหลายตัวเองเลยอัตโนมัติ ซึ่งเรียกว่า Spill ซึ่งเป็นความสามารถของ Excel 365 ที่เรียกว่า Dynamic Array ครับ ใครสนใจลองอ่านบทความนี้เพิ่มเติมได้

ถ้าอยากได้ผลลัพธ์หลายคอลัมน์แบบต่อเนื่องกัน ก็แค่ทำให้ array ผลลัพธ์มีหลายคอลัมน์แค่นั้นเอง

=FILTER(B3:E14,C3:C14=G3)

แต่ถ้ากรณีอยากได้ผลลัพธ์หลายคอลัมน์ที่ไม่ต่อกัน ก็จะต้องพลิกแพลงมากขึ้น ดังนี้

1.1.1 วิธีใช้ FILTER + FILTER

เราสามารถใช้ FILTER 2 รอบ เพื่อเลือกเฉพาะบางคอลัมน์ที่ต้องการได้ด้วยครับ (ขอบคุณ Excel Wizard สำหรับเทคนิคนี้)

=FILTER(FILTER(input สูตร Filter แถว),{เลือกว่าเอาคอลัมน์ไหนบ้าง เป็น 1,0 หรือ TRUE,FALSE})

ตรง Array ผลลัพธ์ผมเปลี่นเป็น B3:E14 ให้คลุมพื้นที่ทั้งหมด ซึ่งมี 4 คอลัมน์
แต่ผมจะเอาแค่ Order id วันที่ ผู้ขาย ซึ่งอยู่คอลัมนืที่ 1,3,4 หรือไม่เอาคอลัมน์ที่ 2 จึงเขียนว่า {1,0,1,1}

ถ้าไม่อยากมานั่งเลือก 1,0 เอง ก็ใช้ MATCH มาช่วยหาว่ามีในเป้าหมายหรือไม่ แล้วใช้ ISNUMBER แปลงเป็น TRUE, FALSE ก็ได้ ดังนี้

=FILTER(FILTER(B3:E14,C3:C14=G3),ISNUMBER(MATCH(B2:E2,H5:J5,0)))
ซึ่งตรง ISNUMBER นั้นถ้าลองกด F9 ดูจะเห็นเป็น {TRUE,FALSE,TRUE,TRUE} ซึ่งก็คือ {1,0,1,1} นั่นเอง

วิธีหลังจะดีกว่าตรงที่มัน Dynamic เปลี่ยนคอลัมน์ที่ต้องการได้ (แต่ลำดับยังเรียงเหมือนเดิมนะครับ)

=FILTER(FILTER(B3:E14,C3:C14=G3),{1,0,1,1})

1.1.2 วิธี FILTER+CHOOSE

สมมติผมอยากจะได้ Order id, ผู้ขาย, วันที่ (สลับเอาวันที่มาอยู่หลัง) ผมคิดว่าใช้ FILTER+CHOOSE น่าจะง่ายสุด ดังนี้

หลักการคือใช้ CHOOSE สร้าตารางจำลองขึ้นมาก่อน แล้วค่อย FILTER ตารางนั้น ซึ่งการสร้างตารางจำลองด้วย CHOOSE สามารถใช้ Array Formula มาช่วยได้ โดยใส่ว่าจะเอากี่คอลัมน์ เช่น เอา 3 คอลัมน์ก็ใส่ {1,2,3} แล้วก็เลือกเลยว่าจะเอาคอลัมน์ไหนเป็นคอลัมน์ 1,2,3

เช่น

=CHOOSE({1,2,3},B3:B14,E3:E14,D3:D14)

แบบนี้จะเป็นการสร้างตารางจำลองขึ้นมาใหม่ได้แล้ว

จากนั้นก็เอา FILTER ไปครอบ ดังนี้

=FILTER(CHOOSE({1,2,3},B3:B14,E3:E14,D3:D14),C3:C14=G3)

เท่านี้เราก็ได้ผลลัพธ์ตามต้องการแล้ว ซึ่งการใช้ FILTER น่าจะเหมาะกับคนที่มี Excel365 เท่านั้น หากใครมี Version เก่ากว่านี้ ก็ไปดูข้อถัดไปได้เลย

1.2 ใช้ VLOOKUP + Helper Column

ในเมื่อ ปัญหาคือมี lookup_value ซ้ำกันหลายตัว ใน table_array ดังนั้นหลักการในการแก้ไขก็คือ ทำให้ข้อมูลไม่ซ้ำกันซะก่อน เช่น แอปเปิ้ล มีซ้ำกัน 4 ตัว ดังนั้นเราจะทำให้มันไม่ซ้ำกัน เช่น ทำให้เป็น แอปเปิ้ล1 แอปเปิ้ล2 แอปเปิ้ล3 แอปเปิ้ล 4 ซะ

ซึ่งมีวิธีทำได้หลายแบบ ซึ่งผมจะใช้เทคนิค COUNTIFS แบบให้จำนวนช่องที่นับมันขยายเพิ่มขึ้นเรื่อยๆ เพื่อให้นับว่า ตั้งแต่ C3 จนถึงบรรทัดของมันเอง มีผลไม้ที่ผมสนใจแล้วกี่อัน (มันก็จะนับสะสมไปเรื่อยๆ)

สังเกตในสูตรช่อง A7 ในรูปข้างล่าง :

=COUNTIFS($C$3:C7,$G$3)

ส่วน criteria_range1 ผมใส่เป็น $C$3:C7 ซึ่งมีการ Fix ตำแหน่งของ C3 ไว้ แต่ไม่ Fix ที่ C7 ทำให้เวลา Copy ลงมาข้างล่าง Range จะขยายเพิ่มขึ้นเรื่อยๆ

ปล. วิธีนี้จะเข้าใจง่ายกว่า 1.3 แต่ว่าจะเปลืองคอลัมน์มากกว่า ยังไงลองเลือกดูนะครับ

ต่อไป เราเอาชื่อสินค้าในบรรทัดนั้นๆ ไปเชื่อมกับเลขลำดับ ด้วยการใช้ &
สูตรในช่อง A3 :

=C3&"-"&COUNTIFS($C$3:C3,$G$3)

(ที่ใช้ – คั่นเพื่อความปลอดภัย เผื่อชื่อสินค้าเป็นตัวเลขแล้วจะงง)

เราก็จะได้ผลลัพธ์แบบนี้

ทีนี้เราก็ทำการ VLOOKUP ได้แล้ว เพราะค่าในคอลัมน์ A ไม่ซ้ำกันเลย (ในผลไม้ที่เราสนใจ) ดังนั้นสูตรใน H6 จะเป็นดังนี้ :

=VLOOKUP($G$3&"-"&G6,A:B,2,FALSE)

ซึ่งในส่วน lookup_value เราเขียนสูตรเอาสินค้าที่สนใจ ไปเชื่อมกับเลขลำดับ ว่า
$G$3&”-“&G6 เพื่อให้ผลลัพธ์ออกมาเหมือนกับในคอลัมน์ A นั่นเอง

ถ้าไม่อยากให้ Error ก็อาจใส่ IFERROR ดักไปก็ได้ เช่น

=IFERROR(VLOOKUP($G$3&"-"&G6,A:B,2,FALSE),"-")

1.3 ใช้ ROW กับ SMALL มาช่วย แบบ Array Formula

ถ้าเราใช้วิธีนี้ ก็จะไม่ต้องสร้างคอลัมน์เพิ่มเลย แต่ก็ต้องมีความเข้าใจเกี่ยวกับ Array Formula พอสมควร

หลักการคือ เราจะใช้ IF + ROW เพื่อ แสดงแถวของค่าที่ตรงกับ lookup value เท่านั้น และใช้ SMALL เพื่อเอาแถวที่น้อยสุดเป็นอันดับที่ 1,2,3 ขึ้นมาแสดง

ก่อนแื่น เราใช้ IF + ROW เพื่อ แสดงแถวของค่าที่ตรงกับ lookup value ก่อน ดังนี้

=IF(C3:C14=G3,ROW(C3:C14))

จะเห็นว่ามันแสดงค่าแถวออกมาเฉพาะลำดับที่ตรงกับแอปเปิ้ล แต่มันยังแสดงแบบเว้นๆ กันอยู่

ต่อไปเราจะใช้ SMALL มาช่วย เพื่อให้มันแสดงค่าที่น้อยสุดเป็นอันดับที่ 1,2,3 และอย่าลืม fix cell reference ด้วย เพราะเดี่ยวจะ copy ลงล่าง

=SMALL(IF($C$3:$C$14=$G$3,ROW($C$3:$C$14)),G6)

เท่านี้ผมก็ได้ลำดับแถวที่ตรงกับแอปเปิ้ลแล้ว คือ 3,4,11,14

จากนั้นผมก็ใช้ INDEX ครอบเข้าไปเพื่อ ดึง Order id จากลำดับแถวที่รู้จาก SMALL+ROW ได้เลย

=INDEX(B:B,SMALL(IF($C$3:$C$14=$G$3,ROW($C$3:$C$14)),G6))

กลุ่ม 2 : ใช้เครื่องมือเพื่อให้เกิดผลลัพธ์หลายค่า

2.1 ใช้ Slicer (Excel 2013 ขึ้นไป)

วิธีนี้จะเรียกว่าโกงก็ได้ มันคือการแปลง Data ให้เป็น Table แล้วใส่ Slicer จบเลย 555

เลือกข้อมูล 1 ช่อง กด Insert –> Table หรือ Ctrl+T

กด Insert Slicer แล้วเลือกสินค้า

จากนั้นเลือกสินค้าที่สนใจได้อย่างง่ายดาย

เนื่องจากวิธีนี้ดูขี้โกงไปหน่อย งั้นแถมวิธีใช้ Power Query ให้ละกันครับ

2.2 ใช้ Power Query (Excel 2013 ขึ้นไป)

ก่อนอื่น เราต้องเอาข้อมูลสินค้าที่เราสนใจเข้า PowerQuery ด้วย ก่อน
ด้วยการ Get Data from Table/Range ดังรูป

ผมตั้งชื่อตารางแรกว่า OrderDatabase แล้ว กด Close & Load to… Only Create Connection เพื่อให้ยังไม่ต้องสร้างตารางผลลัพธ์ออกมา

จากนั้น เอาข้อมูลสินค้าที่เราสนใจเข้า PowerQuery ด้วย Get Data From Table อีกที

แล้วตั้งชื่อว่า SelectedProduct แล้ว Close&Load to… only create connection อีกที

จากนั้นเราจะใช้ Merge Query ดึงข้อมูลมาเฉพาะสิ่งที่สนใจ

ให้เราไปสร้าง Merge Query ขึ้นมาใหม่

จากนั้นก็เลือกทั้งสองตาราง แล้วเลือกคอลัมน์ที่เป็นตัวเชื่อมซะ ซึ่งก็คือสินค้า แล้วเลือก Join Kind เป็น Inner (แปลว่าต้องเจอค่าในทั้ง 2 ตาราง) แล้ว กด ok

กดปุ่มขวาบนตามรูป และติ๊กว่าไม่เอา prefix (เพราะมันอ่านยาก)

ลบคอลัมน์ที่ไม่ต้องการซะ เช่น สินค้า แล้วถ้าต้องการเลข running ก็ Add Index Column From 1 ได้

กด Close & Load to … Table แล้วเลือกให้วางยัง Existing Worksheet ตรงที่ต้องการ แล้วกด ok จบ

จากนั้นเราก็จะได้ผลลัพธ์ออกมาครับ

แต่วิธีนี้มีข้อเสียคือ ถ้าเปลี่ยนสินค้าแล้ว อย่าลืมกดคลิ๊กขวาที่ตารางแล้ว Refresh นะ (ยกเว้นจะผูกกับ VBA ก็อาจจะช่วยได้)

เพียงเท่านี้ก็เสร็จแล้วครับ

สรุปสารพัดวิธี VLOOKUP ผลลัพธ์หลายค่า

เพื่อนๆ ชอบวิธีไหนในการได้ผลลัพธ์หลายค่ามากกว่ากันเอ่ย? หรือใครมีวิธีที่ดีกว่านี้ก็แชร์กันได้นะครับ ^^ ส่วนตัวแล้วผมจะต้องจัดการทุกอย่างใน Power Query ก่อนจะส่งไป Pivot อยู่แล้ว ดังนั้นผมจึงชอบวิธี Power Query มากที่สุดแล้วครับ เพราะทั้งง่ายและทรงพลัง