VLOOKUP

ค้นหาค่าในคอลัมน์แรกของอาร์เรย์ตาราง แล้วส่งกลับค่าในแถวเดียวกันจากคอลัมน์อื่นในตาราง

V ในคำว่า VLOOKUP ย่อมาจาก vertical (หรือแนวตั้ง) ใช้ VLOOKUP แทน HLOOKUP เมื่อค่าเปรียบเทียบของคุณอยู่ในคอลัมน์ทางด้านซ้ายของข้อมูลที่คุณต้องการค้นหา

ไวยากรณ์

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

lookup_value    ค่าที่จะค้นหาในคอลัมน์แรกของตารางอาร์เรย์ (อาร์เรย์: ใช้ในการสร้างสูตรที่จะให้ผลลัพธ์หลายค่า หรือใช้กับกลุ่มของอาร์กิวเมนต์ที่จัดเรียงอยู่ในรูปแถวและคอลัมน์ ช่วงอาร์เรย์จะใช้สูตรเดียวกัน ส่วนค่าคงที่อาร์เรย์ คือ กลุ่มของค่าคงที่ที่ใช้เป็นอาร์กิวเมนต์) ทั้งนี้ Lookup_value สามารถเป็นได้ทั้งค่าหรือการอ้างอิง หาก lookup_value น้อยกว่าค่าที่น้อยที่สุดในแถวแรกของ table_array ย่อมทำให้ VLOOKUP คืนค่าความผิดพลาด #N/A

table_array    คอลัมน์ข้อมูลสองคอลัมน์หรือมากกว่า ใช้การอ้างอิงถึงช่วงหรือชื่อของช่วง ค่าในคอลัมน์แรกของ table_array คือค่าที่ค้นหาด้วย lookup_value ค่าเหล่านี้อาจเป็นข้อความ ตัวเลข หรือค่าทางตรรกะ ข้อความที่ใช้ตัวพิมพ์ใหญ่และตัวพิมพ์เล็กจะเทียบเท่ากัน

col_index_num    หมายเลขคอลัมน์ใน table_array ซึ่งค่าภายในที่คุณต้องการจะถูกส่งกลับมา col_index_num ของ 1 จะส่งกลับค่าในคอลัมน์แรกใน table_array ส่วน col_index_num ของ 2 จะส่งกลับค่าคอลัมน์ที่สองใน table_array และอื่นๆ ถ้า col_index_num:

• น้อยกว่า 1, VLOOKUP จะคืนค่าความผิดพลาด #VALUE!
• มากกว่าจำนวนคอลัมน์ใน table_array ย่อมทำให้ VLOOKUP คืนค่าความผิดพลาด #REF!

range_lookup    ค่าตรรกะที่ระบุว่าคุณต้องการให้ VLOOKUP ค้นหาการจับคู่ที่ตรงกันหรือการจับคู่ที่เหมาะสม:

• ถ้าเป็น TRUE หรือไม่ใส่ค่าอะไรไว้ จะส่งการจับคู่ที่ตรงกันหรือที่เหมาะสมกลับมา ถ้าไม่พบการจับคู่ที่ตรงกัน ค่าที่ใกล้เคียงที่สุดที่น้อยกว่าค่า lookup_value จะถูกส่งกลับมา

ค่าในคอลัมน์แรกของ table_array ต้องเรียงตามลำดับจากน้อยไปหามาก ไม่เช่นนั้น VLOOKUP อาจไม่ได้ให้ค่าที่ถูกต้อง คุณสามารถเรียงค่าตามลำดับจากน้อยไปหามากได้ด้วยการเลือกคำสั่ง เรียงลำดับ จากเมนู ข้อมูล และเลือก จากน้อยไปหามาก สำหรับข้อมูลเพิ่มเติม โปรดดูที่ การเรียงลำดับเริ่มต้น

• หาก FALSE ย่อมทำให้ VLOOKUP ค้นหาเฉพาะการจับคู่ที่ตรงกัน ในกรณีนี้ ค่าในคอลัมน์แรกของ table_array ไม่จำเป็นต้องเรียงลำดับ หากมีค่าสองค่าหรือมากกว่าในคอลัมน์แรกของ table_array ที่ตรงกับ lookup_value ค่าแรกที่พบจะถูกนำมาใช้ หากไม่พบการจับคู่ที่ตรงกัน ค่าความผิดพลาด #N/A จะถูกส่งคืน

หมายเหตุ

• เมื่อค้นหาค่าของข้อความในคอลัมน์แรกของ table_array ดูให้แน่ใจว่า ข้อมูลในคอลัมน์แรกของ table_array ไม่มีเว้นวรรคนำ เว้นวรรคต่อท้าย ไม่มีการใช้เครื่องหมายอัญประกาศแบบตรง ( ' หรือ " ) และแบบโค้ง ( ‘ หรือ “) สลับกัน หรือ อักขระที่ไม่สามารถพิมพ์ได้ เพราะในกรณีเหล่านี้ VLOOKUP อาจให้ค่าที่ไม่ถูกต้องหรือไม่คาดคิดได้ สำหรับข้อมูลเพิ่มเติมเกี่ยวกับฟังก์ชันต่างๆ ที่คุณสามารถนำมาใช้เพื่อทำความสะอาดข้อมูลของข้อความ โปรดดูที่ ฟังก์ชันข้อความและข้อมูล
• เมื่อค้นหาค่าที่เป็นตัวเลขหรือค่าที่เป็นวันที่ ดูให้แน่ใจว่า ข้อมูลในแถวแรกของ table_array ไม่ได้ถูกจัดเก็บไว้ในรูปค่าของข้อความ เพราะในกรณีนี้ VLOOKUP อาจให้ค่าที่ไม่ถูกต้องหรือไม่คาดคิดได้ สำหรับข้อมูลเพิ่มเติม โปรดดูที่ การแปลงตัวเลขที่จัดเก็บไว้ในรูปของข้อความเป็นตัวเลข
• หาก range_lookup เป็น FALSE และ lookup_value เป็นข้อความ คุณจะสามารถใช้อักขระตัวแทน เครื่องหมายคำถาม (?) และเครื่องหมายดอกจัน (*) ใน lookup_value เครื่องหมายคำถามจะจับคู่กับอักขระตัวเดียว เครื่องหมายดอกจันจะจับคู่กับลำดับใดๆ ของอักขระ หากคุณต้องการค้นหาเครื่องหมายคำถามหรือเครื่องหมายดอกจันจริงๆ ให้พิมพ์เครื่องหมายการเว้นคำ (~) นำหน้าอักขระ

ตัวอย่างที่ 1

คุณจะเข้าใจตัวอย่างได้ง่ายขึ้น หากคุณทำการคัดลอกไปไว้ในแผ่นงานเปล่า

วิธีคัดลอกตัวอย่าง

ตัวอย่างนี้จะค้นหาคอลัมน์ความหนาแน่น ตารางคุณสมบัติของชั้นบรรยากาศ เพื่อค้นหาค่าที่ตรงกันในคอลัมน์ความหนืด และอุณหภูมิ (ค่าสำหรับอากาศที่ 0 องศาเซลเซียส ณ ระดับน้ำทะเล หรือบรรยากาศ 1)

1 2 3 4 5 6 7 8 9 10

A B C ความหนาแน่น ความหนืด อุณหภูมิ  .457 3.55 500 .525 3.25 400 .616 2.93 300 .675 2.75 250 .746 2.57 200 .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(.7,A2:C10,3,FALSE) ใช้การจับคู่ที่เหมาะสม ค้นหาค่า .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)

ตัวอย่างที่ 2

คุณจะเข้าใจตัวอย่างได้ง่ายขึ้น หากคุณทำการคัดลอกไปไว้ในแผ่นงานเปล่า

วิธีคัดลอกตัวอย่าง

ตัวอย่างนี้ค้นหาคอลัมน์รหัสสินค้าของตารางผลิตภัณฑ์เพื่อเด็กอ่อน และจับคู่ค่าในคอลัมน์ ต้นทุน และ สินค้า เพื่อคำนวณราคาและเงื่อนไขการทดสอบ

1 2 3 4 5 6

A B C D รหัสสินค้า สินค้า ต้นทุน สินค้า ST-340 รถเข็นเด็ก $145.67 30% BI-567 ผ้ากันเปื้อนสำหรับเด็ก $3.56 40% DI-328 ผ้าอ้อม $21.45 35% WI-989 ผ้าเช็ดทำความสะอาด $5.12 40% AS-469 เครื่องช่วยหายใจ $2.56 45% สูตร คำอธิบาย (ผลลัพธ์) = VLOOKUP("DI-328", A2:D6, 3, FALSE) * (1 + VLOOKUP("DI-328", A2:D6, 4, FALSE)) คำนวณราคาขายปลกของผ้าอ้อมได้ด้วยการเพิ่มเปอร์เซ็นต์สินค้าลงในต้นทุน ($28.96) = (VLOOKUP("WI-989", A2:D6, 3, FALSE) * (1 + VLOOKUP("WI-989", A2:D6, 4, FALSE))) * (1 - 20%) คำนวณราคาขายผ้าเช็ดทำความสะอาดได้ด้วยการลบส่วนลดที่กำหนนดออกจากราคาขายปลีก ($5.73) = หาก(VLOOKUP(A2, A2:D6, 3, FALSE) >= 20, "สินค้าคือ " & 100 * VLOOKUP(A2, A2:D6, 4, FALSE) &"%", "ต้นทุนต่ำกว่า $20.00") หากต้นทุนของสินค้าสูงกว่าหรือเท่ากับ $20.00 ให้แสดงข้อความ "สินค้าเป็น nn%"; หรือแสดงข้อความ "ต้นทุนต่ำกว่า $20.00" (สินค้าคือ 30%) = หาก(VLOOKUP(A3, A2:D6, 3, FALSE) >= 20, "สินค้าคือ: " & 100 * VLOOKUP(A3, A2:D6, 4, FALSE) &"%", "ต้นทุนคือ $" & VLOOKUP(A3, A2:D6, 3, FALSE)) หากต้นทุนสินค้าสูงกว่าหรือเท่ากับ $20.00 ให้แสดงข้อความ สินค้าคือ nn%" หรือแสดงข้อความ "ต้นทุนคือ $n.nn" (ต้นทุนคือ $3.56)

ตัวอย่างที่ 3

คุณจะเข้าใจตัวอย่างได้ง่ายขึ้น หากคุณทำการคัดลอกไปไว้ในแผ่นงานเปล่า

วิธีคัดลอกตัวอย่าง

ตัวอย่างนี้ค้นหาคอลัมน์ ID ของตารางพนักงาน และจับคู่ค่าในคอลัมน์อื่น เพื่อคำนวณอายุและทดสอบหากเงื่อนไขความผิดพลาด

1 2 3 4 5 6 7

A B C D E ID นามสกุล ชื่อ ตำแหน่ง วันเกิด 1 Davolio Nancy ตัวแทนขาย 12/8/1968 2 Fuller Andrew รองประธาน, ฝ่ายขาย 2/19/1952 3 Leverling Janet ตัวแทนขาย 8/30/1963 4 Peacock Margaret ตัวแทนขาย 9/19/1958 5 Buchanan Steven ผู้จัดการฝ่ายขาย 3/4/1955 6 Suyama Michael ตัวแทนขาย 7/2/1963 สูตร คำอธิบาย (ผลลัพธ์) =INT(YEARFRAC(DATE(2004,6,30), VLOOKUP(5,A2:E7,5, FALSE), 1)) สำหรับปีงบประมาณ 2004 ให้ค้นหาอายุของพนักงานที่มี ID เท่ากับ 5 ใช้ฟังก์ชัน YEARFRAC เพื่อลบวันเดือนปีเกิดออกจากวันสิ้นสุดของปีงบประมาณ และแสดงผลลัพธ์เป็นจำนวนเต็มโดยใช้ฟังก์ชัน INT (49) =หาก(ISNA(VLOOKUP(5,A2:E7,2,FALSE)) = TRUE, "ไม่พบพนักงาน", VLOOKUP(5,A2:E7,2,FALSE)) หากมีพนักงานที่มี ID เป็น 5 ให้แสดงนามสกุลของพนักงาน หรือแสดงข้อความ "ไม่พบพนักงาน" (Buchanan) ฟังก์ชัน ISNA ส่งคืนค่า TRUE เมื่อฟังก์ชัน VLOOKUP ส่งคืนค่าความผิดพลาด #NA =หาก(ISNA(VLOOKUP(15,A3:E8,2,FALSE)) = TRUE, "ไม่พบพนักงาน", VLOOKUP(15,A3:E8,2,FALSE)) หากมีพนักงานที่มี ID เป็น 15 ให้แสดงนามสกุลของพนักงาน หรือแสดงข้อความ "ไม่พบพนักงาน" (ไม่พบพนักงาน) ฟังก์ชัน ISNA ส่งคืนค่า TRUE เมื่อฟังก์ชัน VLOOKUP ส่งคืนค่าความผิดพลาด #NA =VLOOKUP(4,A2:E7,3,FALSE) & " " & VLOOKUP(4,A2:E7,2,FALSE) & " คือ " & VLOOKUP(4,A2:E7,4,FALSE) & "." สำหรับพนักงานที่มี ID เป็น 4 ให้รวมค่าของเซลล์สามเซลล์เป็นประโยคที่สมบูรณ์ (Margaret Peacock คือตัวแทนขาย)

 หมายเหตุ   สูตรแรกในตัวอย่างข้างต้นใช้ฟังก์ชัน YEARFRAC หากฟังก์ชันนี้ไม่มี และส่งคืนความผิดพลาด #NAME? ให้ติดตั้งและดาวน์โหลด Analysis ToolPak add-in

วิธีการ