หลายๆ คนคงใช้ VLOOKUP หรือ MATCH แบบ Exact Match กันจนชินแล้ว สูตรพวกนี้จะคืนค่าตัวแรกที่เจอ (นับจากบนลงล่าง) แต่หลายสถานการณ์เราต้องการ “ค่าตัวล่าสุด/ตัวสุดท้าย” มากกว่านะครับ ผมเจอบ่อยมาก 😅
โดยเฉพาะตารางที่เรียงวันที่/เวลาเก่า → ใหม่ เราย่อมอยากได้รายการล่าสุดมากกว่าบรรทัดแรกใช่มั้ยครับ? เช่นข้อมูลแบบนี้เลย
บทความนี้ผมรวบวิธี Lookup ค่าตัวล่าสุดไว้ให้ครบ ตั้งแต่วิธีง่ายสุด ไปจนถึงสูตรที่ยืดหยุ่นกว่านิดนึงครับ 💡
กรณีที่ 1 : กรณียอมให้เรียง Data ใหม่ได้ทั้งหมด
ถ้าเรียงใหม่ได้เลย วิธีง่ายสุดคือ sort ให้วันที่/เวลาใหม่สุดขึ้นไปอยู่ข้างบน
แบบนี้เราก็ VLOOKUP แบบ Exact Match ได้เลย
=VLOOKUP(G2,C:D,2,FALSE)
กรณีที่ 2 : กรณียอมให้เรียง Product ใหม่ได้ แต่ใน Product เดียวกันวันเวลาต้องเรียงจากเก่าไปใหม่
สมมติจัดเรียงได้แบบนี้
แบบนี้ใช้ VLOOKUP แบบ Approximate Match ได้เลย เพราะมันจะคืนค่าตัวสุดท้ายที่น้อยกว่าหรือเท่ากับ lookup_value อยู่แล้ว
=VLOOKUP(G2,C:D,2,TRUE)
แต่สูตรนี้จะใช้ได้ ต้องมั่นใจว่า lookup_value มีอยู่จริงในฐานข้อมูลนะครับ ไม่งั้นมันหลอกเราได้
เช่น lookup_value เป็นคำว่า “คำนี้ไม่มีซะหน่อย” ที่ควรได้ #N/A ก็ดันเจออยู่ดี
(เพราะ Approximate Match จะหยิบค่าตัวสุดท้ายที่น้อยกว่าหรือเท่ากับค่า lookup_value มาให้เรา)
ถ้าอยากชัวร์ ให้ดึงค่าคอลัมน์แรกกลับมาแล้วใช้ IF เช็คอีกที (คล้ายเทคนิค VLOOKUP เร็ว 100 เท่า) เช่นนี้ครับ
=IF(VLOOKUP(G2,C:D,1,TRUE)=G2,VLOOKUP(G2,C:D,2,TRUE),NA())
กรณีที่ 3 : กรณีห้ามเรียงอะไรทั้งสิ้น ต้องเรียงเหมือนเดิม คือตามวันเวลาอย่างเดียวจากเก่าไปใหม่
ถ้าเป็น Excel Version เก่า (กว่า Excel 365)
ถ้าใช้ Excel เวอร์ชันเก่า วิธีคลาสสิกคือใช้สูตรแบบ Array ช่วย ซึ่งยืดหยุ่นมาก เพราะกำหนดเงื่อนไขได้แทบทุกแบบ
=LOOKUP(lookup_value,lookup_vector,[result_vector])
=LOOKUP(2,1/(เงื่อนไข),$D$2:$D$11) =LOOKUP(2,1/($C$2:$C$11=G2),$D$2:$D$11)
LOOKUP ทำงานแบบ Approximate Match เสมอ เราเลยค้นหาเลข 2
จากตาราง lookup_vector ที่เราสร้างให้มีแค่ 1 กับ Error ด้วยเงื่อนไขนี้
=1/($C$2:$C$11=G2) ซึ่ง 1/TRUE ได้ 1, 1/FALSE ได้ Error
พอ lookup ด้วย 2 เราจะได้ตำแหน่งที่มีค่า 1 ตัวสุดท้าย แล้วค่อยดึงผลลัพธ์จากคอลัมน์ D กลับมา (ข้อดีของ LOOKUP คือแยก lookup_vector กับ result_vector ได้)
ถ้ามี Excel 365
ใช้ XLOOKUP ตัว LOOKUP สุดโกง
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
match_mode
- 0 – Exact match. If none found, return #N/A. This is the default.
- -1 – Exact match. If none found, return the next smaller item.
- 1 – Exact match. If none found, return the next larger item.
- 2 – A wildcard match where *, ?, and ~ have special meaning.
search_mode
- 1 – Perform a search starting at the first item. This is the default.
- -1 – Perform a reverse search starting at the last item.
- 2 – Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.
- -2 – Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.
ดังนั้นเราจะใช้สูตรนี้ครับ
=XLOOKUP(G2,$C$2:$C$11,$D$2:$D$11,"-",0,-1) จบเลย
ใช้ FILTER
ใน Excel 365 เราใช้ FILTER ดึงรายการเฉพาะ product = A ออกมาก่อน (Spill Array) ได้เลยครับ
=FILTER(D2:D11,C2:C11=G2)
ถ้ามีฟังก์ชัน TAKE (Microsoft 365 รุ่นใหม่) เอาตัวสุดท้ายได้สั้นๆ แบบนี้เลยครับ
=TAKE(FILTER($D$2:$D$11,$C$2:$C$11=G2),-1)
และถ้าอยากกำหนดลำดับที่เท่าไหร่ก็ได้ วิธี FILTER ยืดหยุ่นมาก เพราะเลือกตำแหน่งได้เอง ด้วยสูตรนี้
=INDEX(FILTER($D$2:$D$11,$C$2:$C$11=G2),เลขลำดับ)
ถ้า Excel ยังไม่มี TAKE หรืออยากใช้วิธีเดิม ก็ใช้ INDEX ได้เหมือนกัน แล้วใช้ COUNTA นับว่ามีผลลัพธ์กี่ตัว (ใช้ COUNTIFS ก็ได้แล้วแต่ชอบ) แล้วเอาไปใส่ในเลขลำดับ
=INDEX(FILTER($D$2:$D$11,$C$2:$C$11=G2),COUNTA(FILTER($D$2:$D$11,$C$2:$C$11=G2)))
หรือใช้ COUNTIFS แบบนี้ก็ได้ครับ
=INDEX(FILTER($D$2:$D$11,$C$2:$C$11=G2),COUNTIFS($C$2:$C$11,G2))
Error #CALC! จะเกิดเมื่อ FILTER แล้วไม่มีผลลัพธ์ ถ้าอยากให้ไม่ขึ้น Error ก็ใช้ IFERROR ดักไว้ เช่นนี้ครับ
=IFERROR(INDEX(FILTER($D$2:$D$11,$C$2:$C$11=G2),COUNTIFS($C$2:$C$11,G2)),"-")
ใช้ Power Query
เอา transaction table เข้า Power Query แล้ว sort ใหม่ (เรียงวันที่ก่อน แล้วค่อยเรียงเวลา) จะได้แบบนี้
จากนั้นให้ add index column ก่อนจะ remove duplicates นะครับ ไม่งั้นมันจะใช้ลำดับเดิมก่อน sort แล้วทำให้เก็บตัวบนสุดผิด 😅
พอได้ index แล้วค่อย remove duplicates ที่คอลัมน์ product ก็จะได้ตัวล่าสุด จบเลยครับ
ได้ตารางนี้แล้วจะเอาไป Merge กับตารางอื่นยังไงก็ได้เลยครับ ส่วนขั้นตอน Merge ผมขอข้ามก่อนนะ 😅
สรุปแล้วคุณชอบแบบไหนครับ?
ผมทำให้ดูหลายทางเลยครับ คุณชอบวิธีไหนสุด? คอมเมนต์บอกกันได้นะ หรือใครมีทริคอื่นก็แชร์มาได้เลยครับ 🙌