เคล็ดลับ: ลองใช้ฟังก์ชัน 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 ร่วมกันแทน
ตัวอย่างนี้แสดงรายการขนาดเล็กที่ค่าที่เราต้องการค้นหา ชิคาโก ไม่ได้อยู่ในคอลัมน์ซ้ายสุด ดังนั้น เราจึงไม่สามารถใช้ VLOOKUP ได้ แต่เราจะใช้ฟังก์ชัน MATCH เพื่อค้นหาชิคาโกในช่วง B1:B11 พบได้ในแถวที่ 4 จากนั้น INDEX จะใช้ค่านั้นเป็นอาร์กิวเมนต์การค้นหา และค้นหาประชากรของ Chicago ในคอลัมน์ที่ 4 (คอลัมน์ D) สูตรที่ใช้จะแสดงในเซลล์ A14
สําหรับตัวอย่างเพิ่มเติมของการใช้ INDEX และ MATCH แทน VLOOKUP ให้ดูบทความ https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ โดย Bill Jelen, Microsoft MVP
คุณควรลองทำดู
ถ้าคุณต้องการทดลองกับฟังก์ชันการค้นหาก่อนที่คุณจะลองใช้กับข้อมูลของคุณเอง ต่อไปนี้เป็นข้อมูลตัวอย่างบางส่วน
ตัวอย่าง 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 |
