สำหรับผู้ที่เชี่ยวชาญในExcelคุณมักจะคุ้นเคยกับฟังก์ชันVLOOKUP ฟังก์ชันVLOOKUPใช้เพื่อค้นหาค่าในเซลล์อื่นตามข้อความที่ตรงกันภายในแถวเดียวกัน

หากคุณยังใหม่ต่อฟังก์ชัน VLOOKUP^(VLOOKUP)คุณสามารถตรวจสอบโพสต์ก่อนหน้าของฉันเกี่ยวกับวิธีใช้ VLOOKUP ใน^{(how to use VLOOKUP in Excel)} Excel

มีประสิทธิภาพเท่าที่เป็นอยู่VLOOKUPมีข้อจำกัดเกี่ยวกับวิธีการจัดโครงสร้างตารางอ้างอิงที่ตรงกันเพื่อให้สูตรทำงาน

บทความนี้จะแสดงข้อจำกัดที่VLOOKUPใช้งานไม่ได้และแนะนำฟังก์ชันอื่นในExcelชื่อINDEX-MATCHที่สามารถแก้ปัญหาได้

INDEX MATCH ตัวอย่าง Excel

โดยใช้ตัวอย่างสเปรดชีต Excel ต่อไปนี้ เรามีรายชื่อเจ้าของรถและชื่อรถ ในตัวอย่างนี้ เราจะพยายามคว้า  รหัสรถ^{(Car ID)}ตามรุ่นรถ^{(Car Model)} ที่ แสดงภายใต้เจ้าของหลายรายดังที่แสดงด้านล่าง:

ในแผ่นงานแยกต่างหากที่เรียกว่าCarTypeเรามีฐานข้อมูลรถยนต์อย่างง่าย  พร้อมรหัส^(ID)รุ่นรถ^{(Car Model)}และสี^(Color)

ด้วยการตั้งค่าตารางนี้  ฟังก์ชัน VLOOKUP^(VLOOKUP)จะทำงานได้ก็ต่อเมื่อข้อมูลที่เราต้องการดึงมาอยู่ในคอลัมน์ทางด้านขวาของสิ่งที่เราพยายามจะจับคู่ ( ฟิลด์โมเดลรถ )^{(Car Model )}

กล่าวอีกนัยหนึ่ง ด้วยโครงสร้างตารางนี้ เนื่องจากเรากำลังพยายามจับคู่ตามรุ่นรถ^{(Car Model)}ข้อมูลเดียวที่เราจะได้รับคือสี^{(Color )} (ไม่ใช่IDเนื่องจาก คอลัมน์ ID  จะอยู่ทางด้านซ้ายของ  คอลัมน์รุ่นรถ )^{(Car Model )}

เนื่องจาก ใน VLOOKUPค่าการค้นหาต้องปรากฏในคอลัมน์แรก และคอลัมน์ค้นหาต้องอยู่ทางขวา ตัวอย่างของเราไม่ตรงตามเงื่อนไขเหล่านั้น

ข่าวดีก็คือINDEX-MATCH  จะสามารถช่วยให้เราบรรลุเป้าหมายนี้ได้ ในทางปฏิบัติ นี่เป็นการรวม ฟังก์ชัน Excel สอง ฟังก์ชันที่สามารถทำงานแยกกันได้: ฟังก์ชัน INDEXและฟังก์ชันMATCH

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

สูตรอาจดูยาวและน่าเกรงขามในตอนแรก อย่างไรก็ตาม เมื่อคุณใช้มันหลายครั้ง คุณจะได้เรียนรู้ไวยากรณ์ด้วยใจ

นี่คือสูตรเต็มในตัวอย่างของเรา:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

นี่คือรายละเอียดสำหรับแต่ละส่วน

=INDEX( – “=”หมายถึงจุดเริ่มต้นของสูตรในเซลล์ และINDEXเป็นส่วนแรกของ ฟังก์ชัน Excelที่เราใช้

CarType!$A$2:$A$5 – คอลัมน์บนแผ่นงานCarType  ที่มีข้อมูลที่เราต้องการเรียก ในตัวอย่างนี้ID  ของรถแต่ละรุ่น^{(Car Model.)}

MATCH( – ส่วนที่สองของ ฟังก์ชัน Excelที่เราใช้

B4 – เซลล์ที่มีข้อความค้นหาที่เรากำลังใช้ ( Car ^(.)Model^{(Car Model)} )

CarType!$B$2:$B$5 – คอลัมน์ในแผ่นงานCarType   ที่มีข้อมูลที่เราจะใช้เพื่อจับคู่กับข้อความค้นหา

0)) – เพื่อระบุว่าข้อความค้นหาต้องตรงกับข้อความในคอลัมน์ที่ตรงกันทุกประการ (เช่น CarType!$B$2:$B$5 ) หากไม่พบการจับคู่แบบตรงทั้งหมด สูตรจะส่งกลับ# #N/A

หมายเหตุ: จำวงเล็บปิดสองครั้งที่ส่วนท้ายของฟังก์ชันนี้ "))" และเครื่องหมายจุลภาคระหว่างอาร์กิวเมนต์^{(Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.)}

โดย ส่วนตัวแล้วฉันได้ย้ายออกจากVLOOKUP และตอนนี้ใช้ INDEX-MATCH เนื่องจากสามารถ ทำได้มากกว่าVLOOKUP

ฟังก์ชันINDEX-MATCHยังมีประโยชน์อื่นๆ เมื่อเปรียบเทียบกับVLOOKUP :

1. คำนวณได้เร็วขึ้น^{(Faster Calculations)}

เมื่อเรากำลังทำงานกับชุดข้อมูลขนาดใหญ่ซึ่งการคำนวณเองอาจใช้เวลานานเนื่องจากมีฟังก์ชัน VLOOKUP^(VLOOKUP) มากมาย คุณจะพบว่าเมื่อคุณแทนที่สูตรทั้งหมดด้วยINDEX-MATCHการคำนวณโดยรวมจะคำนวณเร็วขึ้น

1. ไม่จำเป็นต้องนับคอลัมน์สัมพัทธ์^{(No Need to Count Relative Columns)}

หากตารางอ้างอิงของเรามีข้อความหลักที่เราต้องการค้นหาในคอลัมน์Cและข้อมูลที่เราจำเป็นต้องได้รับอยู่ในคอลัมน์AQเราจะต้องทราบ/นับจำนวนคอลัมน์ระหว่างคอลัมน์ C และคอลัมน์ AQ เมื่อใช้VLOOKUP .

ด้วย  ฟังก์ชัน INDEX-MATCHเราสามารถเลือกคอลัมน์ดัชนีได้โดยตรง (เช่น คอลัมน์ AQ) ซึ่งเราต้องการรับข้อมูลและเลือกคอลัมน์ที่จะจับคู่ (เช่น คอลัมน์ C)

1. มันดูซับซ้อนกว่า^{(It Looks More Complicated)}

VLOOKUPเป็นเรื่องธรรมดาในปัจจุบัน แต่มีไม่มากที่รู้เกี่ยวกับการใช้ฟังก์ชัน INDEX-MATCH ร่วมกัน

สตริงที่ยาวกว่าใน ฟังก์ชัน INDEX-MATCHช่วยให้คุณดูเหมือนผู้เชี่ยวชาญในการจัดการฟังก์ชันExcel ที่ซับซ้อนและขั้นสูง ^(Excel)สนุก!

When to Use Index-Match Instead of VLOOKUP in Excel

For those of yоu who are well-versed in Excel, you аre most likely very familiar with the VLOOKUP function. The VLOOKUP function is used to find a value in a different cell based on some matching text within the same row.

If you are still new to VLOOKUP function, you can check out my previous post on how to use VLOOKUP in Excel.

As powerful as it is, VLOOKUP has a limitation on how the matching reference table needs to be structured in order for the formula to work.

This article will show you the limitation where VLOOKUP cannot be used and introduce another function in Excel called INDEX-MATCH that can solve the issue.

INDEX MATCH Excel Example

Using the following example Excel spreadsheet, we have a list of car owners name and the car name. In this example, we will be trying to grab the Car ID based on the Car Model listed under multiple owners as shown below:

On a separate sheet called CarType, we have a simple car database with the ID, Car Model and Color.

With this table setup, the VLOOKUP function can only work if the data that we want to retrieve is located on the column to the right of what we are trying to match (Car Model field).

In other words, with this table structure, since we are trying to match it based on the Car Model, the only information that we can get is Color (Not ID as the ID column is located to the left of the Car Model column.)

This is because with VLOOKUP, the lookup value must appear in the first column and the lookup columns have to be to the right. None of those conditions are met in our example.

The good news is, INDEX-MATCH will be able to help us in achieving this. In practice, this is actually combining two Excel functions that can work individually: INDEX function and MATCH function.

However, for the purpose of this article, we will only talk about the combination of the two with the aim of replicating the function of VLOOKUP.

The formula can seem to be a little bit long and intimidating at first. However, once you have used it several times, you will learn the syntax by heart.

This is the full formula in our example:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

Here is the breakdown for each section

=INDEX( – The “=” indicates the beginning of formula in the cell and INDEX is the first part of the Excel function that we are using.

CarType!$A$2:$A$5 – the columns on sheet CarType where the data we would like to retrieve is contained. In this example, the ID of each Car Model.

MATCH( – The second part of the Excel function that we are using.

B4 – The cell that contain search text that we are using (Car Model).

CarType!$B$2:$B$5 – The columns on sheet CarType  with the data which we will use to match against the search text.

0)) – To indicate that the search text has to exactly match with the text in the matching column (i.e. CarType!$B$2:$B$5). If the exact match is not found, the formula returns #N/A.

Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.

Personally I have moved away from VLOOKUP and now use INDEX-MATCH as it is capable of doing more than VLOOKUP.

The INDEX-MATCH functions also have other benefits as compared to VLOOKUP:

1. Faster Calculations

When we are working with large datasets where the calculation itself can take a long time due to many VLOOKUP functions, you will find that once you replace all of those formulas with INDEX-MATCH, the overall calculation will be compute faster.

1. No Need to Count Relative Columns

If our reference table is having the key text that we want to search in column C and the data that we need to get is in column AQ, we will need to know/count how many columns are between column C and column AQ when using VLOOKUP.

With the INDEX-MATCH functions, we can directly select the index column (i.e column AQ) where we need to get the data and select the column to be matched (i.e. column C).

1. It Looks More Complicated

VLOOKUP is quite common nowadays, but not many know about using the INDEX-MATCH functions together.

The longer string in INDEX-MATCH function help to make you look like an expert in handling complex and advanced Excel functions. Enjoy!

Related posts

• วิธีแก้ไขข้อผิดพลาด #N/A ในสูตร Excel เช่น VLOOKUP

• วิธีการลบบรรทัดว่างใน Excel

• วิธีใช้ฟีเจอร์ Speak Cells ของ Excel

• วิธีแทรกแผ่นงาน Excel ลงใน Word Doc

• วิธีใช้การวิเคราะห์แบบ What-If ของ Excel

• วิธีผสานเซลล์ คอลัมน์ และแถวใน Excel

• ทำไมคุณควรใช้ช่วงที่มีชื่อใน Excel

• 2 วิธีในการใช้ฟังก์ชัน Transpose ของ Excel

• วิธีใช้คำสั่ง if และ Nested If ใน Excel

• วิธีลบแถวที่ซ้ำกันใน Excel

• วิธีการเขียนสูตร IF/คำสั่งใน Excel

• วิธีจัดเรียงตามวันที่ใน Excel

• วิธีการเรียงตามตัวอักษรใน Excel

• วิธีสร้างรายการแบบหล่นลงที่เชื่อมโยงหลายรายการใน Excel

• วิธีการคำนวณคะแนน Z ใน Excel

• การเชื่อมต่อ Excel กับ MySQL

• วิธีใช้ Flash Fill ใน Excel

• วิธีการสร้างแผนภูมิวงกลมใน Excel

• สะกดตัวเลขอย่างรวดเร็วใน Word และ Excel

• การใช้เครื่องมือค้นหาเป้าหมายการวิเคราะห์แบบ What-If ของ Excel