เคล็ดลับ
ลองใช้ฟังก์ชัน XLOOKUP และ XMATCH ใหม่ ซึ่งเป็นเวอร์ชันที่ได้รับการปรับปรุงของฟังก์ชันที่อธิบายไว้ในบทความนี้ ฟังก์ชันใหม่เหล่านี้จะทํางานในทุกทิศทางและส่งกลับรายการที่ตรงกันตามค่าเริ่มต้น ทําให้ใช้งานง่ายและสะดวกมากกว่าฟังก์ชันรุ่นก่อน
สมมติว่าคุณมีรายการของหมายเลขที่ตั้งสํานักงาน และคุณต้องการทราบว่าในแต่ละสํานักงานมีพนักงานคนใดอยู่บ้าง สเปรดชีตมีขนาดใหญ่มาก ดังนั้นคุณอาจคิดว่าเป็นงานที่ท้าทาย จริงๆแล้วมันค่อนข้างง่ายที่จะทําด้วยฟังก์ชั่นการค้นหา
ฟังก์ชัน VLOOKUP และ HLOOKUP พร้อมกับ INDEX และ MATCH เป็นหนึ่งในฟังก์ชันที่มีประโยชน์ที่สุดใน Excel
หมายเหตุ
ฟีเจอร์ตัวช่วยสร้างการค้นหาจะไม่พร้อมใช้งานใน Excel อีกต่อไป
ต่อไปนี้เป็นตัวอย่างของวิธีการใช้ VLOOKUP
=VLOOKUP(B2,C2:E7,3,TRUE)
ในตัวอย่างนี้ B2 เป็น อาร์กิวเมนต์แรก ซึ่งเป็นองค์ประกอบของข้อมูลที่ฟังก์ชันจําเป็นต้องใช้เพื่อทํางาน สําหรับ VLOOKUP อาร์กิวเมนต์แรกนี้คือค่าที่คุณต้องการค้นหา อาร์กิวเมนต์นี้อาจเป็นการอ้างอิงเซลล์หรือค่าคงที่ เช่น "smith" หรือ 21,000 อาร์กิวเมนต์ที่สองคือช่วงของเซลล์ C2-:E7 ที่จะค้นหาค่าที่คุณต้องการค้นหา อาร์กิวเมนต์ที่สาม คือ คอลัมน์ในช่วงของเซลล์ที่มีค่าที่คุณต้องการ
อาร์กิวเมนต์ที่สี่จะมีหรือไม่ก็ได้ ใส่ TRUE หรือ FALSE ถ้าคุณใส่ TRUE หรือปล่อยว่าง ฟังก์ชันจะส่งกลับค่าที่ใกล้เคียงกับค่าที่คุณระบุในอาร์กิวเมนต์แรก ถ้าคุณใส่ FALSE ฟังก์ชันจะจับคู่ค่าที่ระบุโดยอาร์กิวเมนต์แรก กล่าวคือ การปล่อยให้อาร์กิวเมนต์ที่สี่ว่างไว้ หรือใส่ TRUE จะให้ความยืดหยุ่นมากกว่า
ตัวอย่างนี้จะแสดงวิธีการทํางานของฟังก์ชัน เมื่อคุณใส่ค่าในเซลล์ B2 (อาร์กิวเมนต์แรก) VLOOKUP จะค้นหาเซลล์ในช่วง C2:E7 (อาร์กิวเมนต์ที่ 2) และส่งกลับค่าที่ใกล้เคียงที่สุดจากคอลัมน์ที่สามในช่วง คือ คอลัมน์ E (อาร์กิวเมนต์ที่ 3)
อาร์กิวเมนต์ที่สี่ว่างเปล่า ฟังก์ชันจะส่งกลับค่าที่ใกล้เคียง ถ้าไม่ได้ คุณจะต้องใส่ค่าใดค่าหนึ่งในคอลัมน์ C หรือ D เพื่อให้ได้ผลลัพธ์
เมื่อคุณคุ้นเคยกับ VLOOKUP แล้ว ฟังก์ชัน HLOOKUP ก็ใช้งานง่ายไม่แพ้กัน คุณใส่อาร์กิวเมนต์แบบเดียวกัน แต่ตัวกรองจะค้นหาในแถวแทนที่จะเป็นคอลัมน์
การใช้ INDEX และ MATCH แทน VLOOKUP
มีข้อจํากัดบางประการในการใช้ VLOOKUP — ฟังก์ชัน VLOOKUP สามารถค้นหาค่าจากซ้ายไปขวาเท่านั้น ซึ่งหมายความว่าคอลัมน์ที่มีค่าที่คุณค้นหาควรอยู่ทางด้านซ้ายของคอลัมน์ที่มีค่าส่งกลับเสมอ ตอนนี้ถ้าสเปรดชีตของคุณไม่ได้สร้างด้วยวิธีนี้ อย่าใช้ VLOOKUP ใช้การผสมฟังก์ชัน INDEX และ MATCH แทน
ตัวอย่างนี้แสดงรายการขนาดเล็กที่ค่าที่เราต้องการค้นหา Chicago ไม่ได้อยู่ในคอลัมน์ซ้ายสุด ดังนั้น เราจึงไม่สามารถใช้ VLOOKUP ได้ แต่เราจะใช้ฟังก์ชัน MATCH เพื่อค้นหา Chicago ในช่วง B1:B11 แทน พบได้ในแถวที่ 4 จากนั้น INDEX จะใช้ค่าดังกล่าวเป็นอาร์กิวเมนต์การค้นหา และค้นหาประชากรสําหรับ Chicago ในคอลัมน์ที่ 4 (คอลัมน์ D) สูตรที่ใช้จะแสดงในเซลล์ A14
คุณควรลองทำดู
ถ้าคุณต้องการทดลองกับฟังก์ชันการค้นหาก่อนที่คุณจะใช้กับข้อมูลของคุณเอง คุณอาจลองกับข้อมูลตัวอย่างต่อไปนี้ได้
ตัวอย่างการทํางานของ VLOOKUP
คัดลอกข้อมูลต่อไปนี้ลงในสเปรดชีตเปล่า
เคล็ดลับ
ก่อนที่คุณจะวางข้อมูลลงใน Excel ให้ตั้งค่าความกว้างคอลัมน์สําหรับคอลัมน์ A ถึง C เป็น 250 พิกเซล แล้วคลิก ตัดข้อความ (แท็บ หน้าแรก กลุ่ม การจัดแนว)
| ความหนาแน่น | ความหนืด | อุณหภูมิ |
|---|---|---|
| 0.457 | 3.55 | 500 |
| 0.525 | 3.25 | 400 |
| 0.606 | 2.93 | 300 |
| 0.675 | 2.75 | 250 |
| 0.746 | 2.57 | 200 |
| 0.835 | 2.38 | 150 |
| 0.946 | 2.17 | 100 |
| 1.09 | 1.95 | 50 |
| 1.29 | 1.71 | 0 |
| สูตร | คำอธิบาย | ผลลัพธ์ |
| =VLOOKUP(1,A2:C10,2) | ใช้การค้นหาค่าที่ตรงกันโดยประมาณ ค้นหาค่า 1 ในคอลัมน์ A พบค่าที่สูงสุดซึ่งน้อยกว่าหรือเท่ากับ 1 ในคอลัมน์ A คือ 0.946 แล้วส่งกลับค่าจากคอลัมน์ B ในแถวเดียวกัน | 2.17 |
| =VLOOKUP(1,A2:C10,3,TRUE) | ใช้การค้นหาค่าที่ตรงกันโดยประมาณ ค้นหาค่า 1 ในคอลัมน์ A พบค่าที่สูงสุดซึ่งน้อยกว่าหรือเท่ากับ 1 ในคอลัมน์ A คือ 0.946 แล้วส่งกลับค่าจากคอลัมน์ C ในแถวเดียวกัน | 100 |
| =VLOOKUP(0.7,A2:C10,3,FALSE) | ใช้การค้นหาค่าที่ตรงกันพอดี ค้นหาค่า 0.7 ในคอลัมน์ A เนื่องจากไม่มีค่าที่ตรงกันพอดีในคอลัมน์ A ข้อผิดพลาดจะถูกส่งกลับ | #N/A |
| =VLOOKUP(0.1,A2:C10,2,TRUE) | ใช้การค้นหาค่าที่ตรงกันโดยประมาณ ค้นหาค่า 0.1 ในคอลัมน์ A เนื่องจาก 0.1 น้อยกว่าค่าที่น้อยที่สุดในคอลัมน์ A ข้อผิดพลาดจะถูกส่งกลับ | #N/A |
| =VLOOKUP(2,A2:C10,2,TRUE) | ใช้การค้นหาค่าที่ตรงกันโดยประมาณ ค้นหาค่า 2 ในคอลัมน์ A พบค่าที่สูงที่สุดที่น้อยกว่าหรือเท่ากับ 2 ในคอลัมน์ A คือ 1.29 แล้วส่งกลับค่าจากคอลัมน์ B ในแถวเดียวกัน | 1.71 |
ตัวอย่าง HLOOKUP
คัดลอกเซลล์ทั้งหมดในตารางนี้ แล้ววางข้อมูลลงในเซลล์ A1 บนเวิร์กชีตเปล่าใน Excel
เคล็ดลับ
ก่อนที่คุณจะวางข้อมูลลงใน Excel ให้ตั้งค่าความกว้างคอลัมน์สําหรับคอลัมน์ A ถึง C เป็น 250 พิกเซล แล้วคลิก ตัดข้อความ (แท็บ หน้าแรก กลุ่ม การจัดแนว)
| เพลา | ตลับลูกปืน | สลักเกลียว |
|---|---|---|
| 4 | 4 | 9 |
| 5 | 7 | 10 |
| 6 | 8 | 11 |
| สูตร | คำอธิบาย | ผลลัพธ์ |
| =HLOOKUP("เพลา", A1:C4, 2, TRUE) | ค้นหา "เพลา" ในแถวที่ 1 และส่งกลับค่าจากแถวที่ 2 ซึ่งอยู่ในคอลัมน์เดียวกัน (คอลัมน์ A) | 4 |
| =HLOOKUP("ตลับลูกปืน", A1:C4, 3, FALSE) | ค้นหา "ตลับลูกปืน" ในแถวที่ 1 และส่งกลับค่าจากแถวที่ 3 ซึ่งอยู่ในคอลัมน์เดียวกัน (คอลัมน์ B) | 7 |
| =HLOOKUP("B", A1:C4, 3, TRUE) | ค้นหา "B" ในแถวที่ 1 และส่งกลับค่าจากแถวที่ 3 ซึ่งอยู่ในคอลัมน์เดียวกัน เนื่องจากไม่พบค่าที่ตรงกันกับ "B" ดังนั้นจะใช้ค่ามากที่สุดในแถวที่ 1 ที่น้อยกว่า "B" ซึ่งก็คือ "เพลา" ใน คอลัมน์ A | 5 |
| =HLOOKUP("สลักเกลียว", A1:C4, 4) | ค้นหา "สลักเกลียว" ในแถวที่ 1 และส่งกลับค่าจากแถวที่ 4 ซึ่งอยู่ในคอลัมน์เดียวกัน (คอลัมน์ C) | 11 |
| =HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE) | ค้นหาเลข 3 ในค่าคงที่อาร์เรย์ที่มีสามแถว และส่งกลับค่าจากแถวที่ 2 ในคอลัมน์เดียวกัน (ในกรณีนี้ คือคอลัมน์ที่สาม) มีแถวของค่าสามแถวในค่าคงที่อาร์เรย์ โดยแต่ละแถวคั่นด้วยเครื่องหมายอัฒภาค (;)) เนื่องจากพบ "c" ในแถวที่ 2 และอยู่ในคอลัมน์เดียวกันกับ 3 ดังนั้นจะส่งกลับ "c" | c |
ตัวอย่าง INDEX และ MATCH
ตัวอย่างสุดท้ายนี้ใช้ฟังก์ชัน INDEX และ MATCH ร่วมกันเพื่อส่งกลับหมายเลขใบแจ้งหนี้แรกสุดและวันที่ที่สอดคล้องกันสําหรับเมืองแต่ละเมืองห้าเมือง เนื่องจากวันที่ถูกส่งกลับเป็นตัวเลข เราจึงใช้ฟังก์ชัน TEXT เพื่อจัดรูปแบบให้เป็นวันที่ ฟังก์ชัน INDEX จะใช้ผลลัพธ์ของฟังก์ชัน MATCH เป็นอาร์กิวเมนต์ การผสมฟังก์ชัน INDEX และ MATCH จะถูกใช้สองครั้งในแต่ละสูตร ครั้งแรก เพื่อส่งกลับหมายเลขใบแจ้งหนี้ และจากนั้นเพื่อส่งกลับวันที่
คัดลอกเซลล์ทั้งหมดในตารางนี้ แล้ววางข้อมูลลงในเซลล์ A1 บนเวิร์กชีตเปล่าใน Excel
เคล็ดลับ
ก่อนที่คุณจะวางข้อมูลลงใน Excel ให้ตั้งค่าความกว้างคอลัมน์สําหรับคอลัมน์ A ถึง D เป็น 250 พิกเซล แล้วคลิกตัดข้อความ (แท็บ หน้าแรก กลุ่ม การจัดแนว)
| ใบแจ้งหนี้ | เมือง | วันที่ใบแจ้งหนี้ | ใบแจ้งหนี้ที่เก่าที่สุดตามชื่อเมือง พร้อมกับวันที่ |
|---|---|---|---|
| 3115 | แอตแลนตา | 4/7/12 | ="แอตแลนตา = "&INDEX($A$2:$C$33,MATCH("แอตแลนตา",$B$2:$B$33,0),1)& ", วันที่ใบแจ้งหนี้: " & TEXT(INDEX($A$2:$C$33,MATCH("แอตแลนตา",$B$2:$B$33,0),3),"m/d/yy") |
| 3137 | แอตแลนตา | 4/9/12 | ="ออสติน = "&INDEX($A$2:$C$33,MATCH("ออสติน",$B$2:$B$33,0),1)& ", วันที่ใบแจ้งหนี้: " & TEXT(INDEX($A$2:$C$33,MATCH("ออสติน",$B$2:$B$33,0),3),"m/d/yy") |
| 3154 | แอตแลนตา | 4/11/12 | ="ดัลลัส = "&INDEX($A$2:$C$33,MATCH("ดัลลัส",$B$2:$B$33,0),1)& ", วันที่ใบแจ้งหนี้: " & TEXT(INDEX($A$2:$C$33,MATCH("ดัลลัส",$B$2:$B$33,0),3),"m/d/yy") |
| 3191 | แอตแลนตา | 4/21/12 | ="นิวออร์ลีนส์ = "&INDEX($A$2:$C$33,MATCH("นิวออร์ลีนส์",$B$2:$B$33,0),1)& ", วันที่ใบแจ้งหนี้: " & TEXT(INDEX($A$2:$C$33,MATCH("นิวออร์ลีนส์",$B$2:$B$33,0),3),"m/d/yy") |
| 3293 | แอตแลนตา | 4/25/12 | ="แทมปา = "&INDEX($A$2:$C$33,MATCH("แทมปา",$B$2:$B$33,0),1)& ", วันที่ใบแจ้งหนี้: " & TEXT(INDEX($A$2:$C$33,MATCH("แทมปา",$B$2:$B$33,0),3),"m/d/yy") |
| 3331 | แอตแลนตา | 4/27/12 | |
| 3350 | แอตแลนตา | 4/28/12 | |
| 3390 | แอตแลนตา | 5/1/12 | |
| 3441 | แอตแลนตา | 5/2/12 | |
| 3517 | แอตแลนตา | 5/8/12 | |
| 3124 | ออสติน | 4/9/12 | |
| 3155 | ออสติน | 4/11/12 | |
| 3177 | ออสติน | 4/19/12 | |
| 3357 | ออสติน | 4/28/12 | |
| 3492 | ออสติน | 5/6/12 | |
| 3316 | ดัลลัส | 4/25/12 | |
| 3346 | ดัลลัส | 4/28/12 | |
| 3372 | ดัลลัส | 5/1/12 | |
| 3414 | ดัลลัส | 5/1/12 | |
| 3451 | ดัลลัส | 5/2/12 | |
| 3467 | ดัลลัส | 5/2/12 | |
| 3474 | ดัลลัส | 5/4/12 | |
| 3490 | ดัลลัส | 5/5/12 | |
| 3503 | ดัลลัส | 5/8/12 | |
| 3151 | นิวออร์ลีนส์ | 4/9/12 | |
| 3438 | นิวออร์ลีนส์ | 5/2/12 | |
| 3471 | นิวออร์ลีนส์ | 5/4/12 | |
| 3160 | แทมปา | 4/18/12 | |
| 3328 | แทมปา | 4/26/12 | |
| 3368 | แทมปา | 4/29/12 | |
| 3420 | แทมปา | 5/1/12 | |
| 3501 | แทมปา | 5/6/12 |
ดูเพิ่มเติม
บัตรอ้างอิงโดยสรุป: ทบทวนเกี่ยวกับ VLOOKUP
ฟังก์ชันการค้นหาและการอ้างอิง (ข้อมูลอ้างอิง)