Thep Excel
HLOOKUPค้นหาข้อมูลแนวนอนในตาราง
HLOOKUP ค้นหาข้อมูลจากแถวแรกของตาราง แล้วคืนค่าจากแถวที่ระบุ ตรงข้ามกับ VLOOKUP ที่ค้นหาแนวตั้ง เหมาะสำหรับตารางที่หัวข้อมูลเรียงแบบแนวนอน
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| lookup_value | Value/Text/Reference | ค่าที่ต้องการค้นหา ซึ่งจะมองหาในแถวแรกของ table_array ได้เป็นค่าตัวเลข ข้อความ หรือการอ้างอิงเซลล์ | |
| table_array | Range/Array | ช่วงข้อมูลตารางแนวนอน แถวแรกจะใช้สำหรับค้นหา (จะต้องเรียงจากน้อยไปมากถ้าใช้ Approximate Match) แถวอื่นๆ จะใช้สำหรับดึงค่า | |
| row_index_num | Number | เลขลำดับแถวใน table_array ที่ต้องการให้ HLOOKUP ดึงค่ามา แถวแรกนับว่าเป็น 1 หากเกินจำนวนแถว จะขึ้น #REF! error | |
| [range_lookup]ไม่บังคับ | Logical (TRUE/FALSE) | TRUE | รูปแบบการค้นหา: TRUE (ค่าเริ่มต้น) ค้นหาค่าใกล้เคียง ต้องให้แถวแรกเรียงจากน้อยไปมาก FALSE ค้นหาค่าตรงกันพอดี หากไม่เจอจะขึ้น #N/A |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
HLOOKUP ใช้สำหรับค้นหาข้อมูลในตารางที่จัดระเบียบแบบแนวนอน (Horizontal Layout) คล้ายๆ กับ VLOOKUP แต่เหมาะกับข้อมูลที่เรียงแบบ “หัวข้อมูลเป็นแถว” เช่น ตารางราคาสินค้าตามขนาด ตารางเงินเดือนตามตำแหน่ง หรือตารางผลการศึกษา
ที่เจ๋งคือ HLOOKUP จริงๆ ทำให้การค้นหาข้อมูลที่วางแนวนอนง่ายขึ้นมาก ไม่ต้องไปจัดโครงสร้างตาราง ก็สามารถค้นหาได้ทันที ลองนึกดูว่าถ้าไม่มี HLOOKUP แล้วข้อมูลเรียงแนวนอน คงต้องใช้ INDEX+MATCH เพื่อหลีกเลี่ยงการเปลี่ยนโครงสร้างตาราง
ส่วนตัวผม ประทับใจกับ HLOOKUP ตอนที่ต้องทำตารางราคาย่อยในการขายรถมอเตอร์ไซค์ ข้อมูลเรียงตามความจุเครื่องยนต์ (110cc, 150cc, 250cc) จากซ้ายไปขวา และแล้วก็มีแถว Price, Stock, Color ซ้อนกันไป HLOOKUP ทำให้หมดปัญหา สามารถค้นหาราคาตามความจุได้เลย
How it works
การค้นหาข้อมูลในตารางสถิติ
เมื่อมีตารางสรุปผลสำรวจ หรือข้อมูลสถิติที่จัดวางในแนวนอน (เช่น แถวแรกเป็นหัวข้อ, แถวสองเป็นค่า) HLOOKUP ช่วยดึงข้อมูลที่ต้องการได้อย่างรวดเร็ว
กำหนดอัตราค่าธรรมเนียม/ส่วนลด
ใช้ HLOOKUP แบบ Approximate Match กับตารางค่าธรรมเนียมหรือส่วนลดตามจำนวนเงินที่ซื้อ หรือช่วงอายุ เพื่อคำนวณอัตราที่ถูกต้อง
Examples
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | รหัสสินค้า | P001 | P002 | P003 | P004 | P005 |
| 2 | ชื่อสินค้า | เมาส์ไร้สาย | คีย์บอร์ด | จอ 27 นิ้ว | หูฟัง | เว็บแคม |
| 3 | สถานะ | ปิด | เปิด | ปิด | เปิด | ปิด |
| 4 | ราคา | 590 | 1,290 | 8,900 | 2,450 | 1,750 |
ตารางนี้เป็นตารางสินค้าเดียวกับหน้าอื่น แต่สลับแกน เพราะ HLOOKUP ค้นหาตามแถวบนสุดของตาราง ไม่ใช่คอลัมน์ซ้ายสุดแบบ VLOOKUP คอลัมน์ A เป็นป้ายชื่อแถว ไม่ใช่ค่าที่ใช้ค้นหา
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | คะแนนขั้นต่ำ | ||||
| 2 | 0 | 50 | 60 | 70 | 80 |
| 3 | F | D | C | B | A |
แถวแรกเป็นป้ายกำกับ ส่วนแถวคะแนนกับแถวเกรดอยู่ด้านล่าง สูตร HLOOKUP ด้านล่างจึงอ้างถึงช่วง A7:E8 ซึ่งบังคับว่าแถวคะแนนต้องเรียงจากน้อยไปมากจากซ้ายไปขวา
ตัวอย่างพื้นฐาน – ค้นหาชื่อสินค้าจากรหัส (Exact Match)HLOOKUP("P003", B1:F4, 2, FALSE)
HLOOKUP ค้นหา "P003" ในแถวบนสุดของ B1:F4 พบในคอลัมน์ที่ 3 แล้วดึงค่าจากแถวที่ 2 ของ table_array (แถวชื่อสินค้า) ได้ "จอ 27 นิ้ว"
สังเกตว่า table_array เริ่มที่คอลัมน์ B ไม่ใช่ A เพราะคอลัมน์ A เป็นป้ายชื่อแถว ไม่ใช่ส่วนหนึ่งของแถวค้นหาจริง
ใส่ FALSE เพื่อบังคับให้ตรงเป๊ะเท่านั้น ถ้าไม่เจอจะได้ #N/A ทันที
ตัวอย่างจริง – ค้นหาราคาสินค้าจากรหัสHLOOKUP("P004", B1:F4, 4, FALSE)
HLOOKUP ค้นหา "P004" ในแถวบนสุด พบในคอลัมน์ที่ 4 แล้วดึงราคาจากแถวที่ 4 ของ table_array (แถวราคา) ได้ 2,450 บาท
ถ้าเปลี่ยน row_index_num เป็น 2 จะได้ชื่อสินค้า "หูฟัง" ถ้าเป็น 3 จะได้สถานะ "เปิด"
หลักการเดียวกับ VLOOKUP เป๊ะ ต่างแค่ตารางวางแนวนอนแทนแนวตั้งครับ
ตัวอย่างเทคนิค – ใช้ Approximate Match หาเกรดจากคะแนนHLOOKUP(65, A7:E8, 2, TRUE)
HLOOKUP ค้นหาคะแนน 65 โดยใช้ range_lookup=TRUE (Approximate Match) จะหาค่าใกล้เคียงสูงสุดในแถวบนของ A7:E8 ที่ยังไม่เกิน 65 ซึ่งคือ 60 แล้วดึงเกรดจากแถวที่ 2 ได้ C
ถ้าคะแนนเป็น 82 จะได้ A เพราะ 82 มากกว่า 80 ซึ่งเป็นค่าสูงสุดในตาราง ถ้าเป็น 45 จะได้ F
เงื่อนไขสำคัญคือแถวบนสุดต้องเรียงจากน้อยไปมากจากซ้ายไปขวาเสมอ ไม่งั้น Excel จะไม่ฟ้องอะไรแต่คืนค่าผิด
ตัวอย่างลึก – HLOOKUP ร่วม MATCH สำหรับความยืดหยุ่นสูงHLOOKUP("P005", B1:F4, MATCH("สถานะ", A1:A4, 0), FALSE)
สูตรนี้ทำงานสองขั้น MATCH("สถานะ", A1:A4, 0) หาว่าป้ายชื่อแถว "สถานะ" อยู่ในแถวที่เท่าไหร่ของตาราง (คือแถวที่ 3 นับรวมแถวรหัสสินค้าเป็นแถวที่ 1)
จากนั้น HLOOKUP ค้นหา "P005" ในแถวบนสุดของ B1:F4 แล้วดึงค่าจากแถวที่ MATCH บอก (แถวที่ 3) ได้ "ปิด"
ถ้าเปลี่ยนคำที่ MATCH ค้นหาเป็น "ราคา" ก็จะได้ 1,750 แทน ข้อดีคือไม่ต้องนับเองว่าแถวไหนคือแถวที่เท่าไหร่ ถ้ามีคนแทรกแถวใหม่เข้ามา สูตรก็ยังหาถูกอยู่ดี
FAQs
HLOOKUP กับ VLOOKUP ต่างกันอย่างไร?+
HLOOKUP ค้นหาแถวแรกแนวนอน ส่วน VLOOKUP ค้นหาคอลัมน์แรกแนวตั้ง เลือกใช้ตามโครงสร้างข้อมูลของคุณ
XLOOKUP ดีกว่า HLOOKUP รึเปล่า?+
ถ้าใช้ Excel 365 หรือ 2021 ขึ้นไป แนะนำให้ใช้ XLOOKUP เพราะมีความยืดหยุ่นสูงกว่า ค้นหาได้ทั้งแนวตั้งและแนวนอน ไม่ต้องนับแถว แต่ถ้าใช้ Excel เก่า HLOOKUP ยังคงใช้ได้
HLOOKUP ใช้ได้กับ Excel เวอร์ชันไหนบ้าง?+
HLOOKUP ใช้ได้ตั้งแต่ Excel 2003 เป็นต้นมา ดังนั้นเกือบทุกเวอร์ชัน Excel ใช้ได้หมด
ทำไม HLOOKUP ขึ้น #N/A?+
ส่วนใหญ่เป็นเพราะ lookup_value ไม่มีในแถวแรก หรือ range_lookup = FALSE แต่ค่านั้นไม่ตรงกันพอดี ตรวจสอบการสะกดหรือใช้ IFERROR
HLOOKUP ทำงานช้าตอนข้อมูลเยอะๆ รึเปล่า?+
HLOOKUP เร็วพอสำหรับข้อมูลทั่วไป แต่ถ้ามีหลักแสน ให้ลองใช้ XLOOKUP หรือ Power Query
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ HLOOKUP จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
GETPIVOTDATA ช่วยดึงข้อมูลจาก PivotTable โดยอ้างอิงชื่อฟิลด์และเงื่อนไข แทนที่จะอ้างอิงตำแหน่งเซลล์
.
เหมาะสำหรับการสร้างรายงานแบบไดนามิกและการวิเคราะห์ข้อมูลที่ต้องการความแม่นยำสูง เพราะสูตรจะยังคงทำงานได้ถูกต้องแม้ PivotTable จะเปลี่ยนแปลง 💡
INDEX คืนค่าหรือ reference ของเซลล์จากตำแหน่งที่ระบุใน range หรือ array โดยอ้างอิงจากหมายเลขแถวและคอลัมน์ที่เราระบุ มักถูกใช้ร่วมกับ MATCH เพื่อสร้าง INDEX-MATCH pattern ที่ยืดหยุ่นกว่า VLOOKUP เยอะ เพราะสามารถดึงข้อมูลจากทิศทางไหนก็ได้ และไม่พังเมื่อมีการแทรกหรือลบคอลัมน์
LOOKUP ค้นหาค่าในช่วงข้อมูลที่เรียงลำดับแล้ว (ascending) และคืนค่าจากตำแหน่งเดียวกันในอีกช่วงหนึ่ง มีเทคนิคพิเศษคือหาค่าสุดท้ายที่ไม่ว่าง แนะนำใช้ XLOOKUP แทนสำหรับ Excel 365
MATCH คืนเลขลำดับตำแหน่งของค่าที่ค้นหาในช่วงข้อมูลแถวเดียวหรือคอลัมน์เดียว รองรับการค้นหา 3 โหมด คือ Exact Match (0) ที่ไม่ต้องเรียงข้อมูล, Approximate Match แบบ Less Than or Equal (1) ที่ต้องเรียงจากน้อยไปมาก, และ Greater Than or Equal (-1) ที่ต้องเรียงจากมากไปน้อย รองรับ Wildcard (* และ ?) ในโหมด Exact Match และมักใช้คู่กับ INDEX เป็นรูปแบบ INDEX-MATCH ที่ยืดหยุ่นกว่า VLOOKUP
VLOOKUP เป็นฟังก์ชันค้นหาแนวตั้งที่ได้รับความนิยมสูงสุดใน Excel โดยค้นหาค่าที่ต้องการในคอลัมน์ซ้ายสุดของตาราง จากนั้นดึงข้อมูลจากคอลัมน์ที่ระบุในแถวเดียวกัน รองรับทั้งการค้นหาแบบตรงทุกตัวอักษร (Exact Match) และแบบประมาณค่า (Approximate Match) สำหรับข้อมูลที่เรียงลำดับ ข้อจำกัดหลักคือต้องค้นหาจากคอลัมน์ซ้ายสุดเท่านั้น ไม่สามารถดึงข้อมูลจากด้านซ้ายของคอลัมน์ค้นหาได้
ฟังก์ชัน XLOOKUP ใช้สำหรับค้นหาข้อมูลในตารางทั้งแนวตั้งและแนวนอน มีความยืดหยุ่นสูงกว่า VLOOKUP โดยสามารถค้นหาจากซ้ายไปขวา ขวาไปซ้าย และกำหนดค่าเริ่มต้นเมื่อไม่พบข้อมูลได้
XMATCH คืนค่าตำแหน่งของข้อมูลที่ค้นหาในช่วงหรืออาร์เรย์ ถือเป็นฟังก์ชันรุ่นใหม่ที่ Microsoft พัฒนามาแก้จุดอ่อนของ MATCH โดยเฉพาะเรื่องค่า Default ที่เป็น Exact Match แทน Approximate Match ทำให้ใช้ง่ายและปลอดภัยกว่ามาก นอกจากนี้ยังรองรับการค้นหาย้อนกลับ Binary Search สำหรับข้อมูลขนาดใหญ่ และการค้นหาแบบ Wildcard ทำให้ยืดหยุ่นกว่า MATCH เดิมหลายเท่า
ยังไม่มีบทความที่เกี่ยวข้องกับฟังก์ชันนี้
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่