การ Lookup ข้อมูลที่มีคำตอบมากกว่า 1 ค่าแล้วต้องการนำมาแสดงทุกค่าเป็นปัญหาที่พบได้บ่อยในลำดับต้น ๆ ของคำถามทั้งหมด งานลักษณะนี้ไม่ใช่ความสามารถของ Vlookup แต่สามารถใช้สูตรแบบ Array เข้ามาช่วยได้ ดังตัวอย่างปัญหาดังภาพด้านล่าง
ที่ชีต ฐานข้อมูล Seller ที่ชื่อ Robert มี Product 2 ตัวอยู่คนละบรรทัดคือ Apricots และ Lemons ต้องการจะดึงทั้ง 2 ค่าไปไว้ที่ชีตคำตอบโดยให้เรียงไว้ทางขวาของชื่อ Robert เราสามารถใช้สูตรในเซลล์ B3 ของชีตคำตอบตามด้านล่าง
=IFERROR(INDEX(ฐานข้อมูล!$B$4:$B$10,SMALL(IF(ฐานข้อมูล!$A$4:$A$10=$A3,ROW(ฐานข้อมูล!$A$4:$A$10)-ROW(ฐานข้อมูล!$A$4)+1),COLUMNS($B3:B3))),"")
ต้องกดแป้นให้รับสูตรแบบ Array คือ Ctrl+Shift+Enter > Copy สูตรไปด้านขวา > Copy สูตรลงด้านล่าง
คำอธิบายสูตร
Index ที่ใช้นี้เป็นแบบ Index(array,row_num) แปลว่าจากข้อมูลใน array ให้นำข้อมูลลำดับที่ row_num มาแสดง
array ในที่นี้คือ INDEX(ฐานข้อมูล!$B$4:$B$10 ส่วน row_num คือ SMALL(IF(ฐานข้อมูล!$A$4:$A$10=$A3,ROW(ฐานข้อมูล!$A$4:$A$10)-ROW(ฐานข้อมูล!$A$4)+1),COLUMNS($B3:B3)) นั่นเอง
Small(array,k) หมายถึง จากข้อมูล array ให้นำค่าที่น้อยที่สุดในลำดับที่ k มาแสดง เทียบกับสูตรข้างต้นจะได้ว่า จาก IF(ฐานข้อมูล!$A$4:$A$10=$A3,ROW(ฐานข้อมูล!$A$4:$A$10)-ROW(ฐานข้อมูล!$A$4)+1) ให้นำค่าที่น้อยที่สุดในลำดับที่ COLUMNS($B3:B3) มาแสดง
จากสูตร IF(ฐานข้อมูล!$A$4:$A$10=$A3,ROW(ฐานข้อมูล!$A$4:$A$10)-ROW(ฐานข้อมูล!$A$4)+1) หมายถึง หาก ฐานข้อมูล!$A$4:$A$10=$A3 เป็นจริงแล้วให้แสดงผลลัพธ์ของ ROW(ฐานข้อมูล!$A$4:$A$10)-ROW(ฐานข้อมูล!$A$4)+1
จากสูตร ROW(ฐานข้อมูล!$A$4:$A$10)-ROW(ฐานข้อมูล!$A$4)+1 หมายถึง ให้แสดงค่าลำดับโดยเริ่มจากเลข 1 เป็นต้นไปจนเท่ากับขนาดของจำนวนบรรทัดในช่วง ฐานข้อมูล!$A$4:$A$10 จะได้ผลลัพธ์เป็นเช่น {1;2;3;4;5;6;7}
ภาพรวมสูตรจะได้เป็นเช่น
Small(If({True;True;False;True;False;False;False},{1;2;3;4;5;6;7}),COLUMNS($B3:B3))
ด้านหน้าที่เป็น True จะนำค่าตัวเลขด้านหลังมาแสดง จะได้เป็นเช่น
Small({1;2;False;4;False;False;False},COLUMNS($B3:B3))
เนื่องจาก COLUMNS($B3:B3) คือจำนวนคอลัมน์ในช่วง $B3:B3 ผลลัพธ์คือ 1 คอลัมน์ ภาพสูตรจะได้เป็นเช่น
Small({1;2;False;4;False;False;False},1)
หมายถึงจากช่วง {1;2;False;4;False;False;False} ให้นำค่าที่น้อยสุดในลำดับที่ 1 มาแสดง ผลลัพธ์จะได้เป็น 1 และเมื่อนำผลลัพธ์นี้ส่งต่อไปยัง Index ภาพรวมของสูตรจะได้เป็น Index(array,1) หมายถึงจาก array ให้นำลำดับที่ 1 มาแสดง
สำหรับ COLUMNS($B3:B3) เมื่อคัดลอกสูตรไปด้านขวาสูตรจะกลายเป็น COLUMNS($B3:C3) ผลลลัพธ์คือ 2 จะเป็นการนำค่าที่น้อยที่สุดในลำดับที่ 2 มาแสดง เช่นนี้เป็นต้น
กรณีมีปัญหาการใช้งาน Excel and VBA สามารถสอบถามได้ที่ snasui.com