Công thức trong Excel là một chương trình thực hiện các phép tính toán trên bảng dữ liệu. Các công thức này thực hiện những phép toán rất chính xác như phép cộng, nhân, hay so sánh các giá trị trong worksheet. Sau đây là bài viết hướng dẫn các hàm lấy dữ liệu có điều kiện trong excel.

Phương pháp dùng HÀM INDEX và  MATCH trong EXCEL cơ bản

Vì bài viết này hội tụ vào những trường hợp sử dụng INDEX và MATCH sẽ thuận lợi hơn việc sử dụng VLOOKUP nên để tìm hiểu phương pháp dùng cơ bản của INDEX và MATCH mời các bạn nhìn thấy video sau đây. File Excel đi kèm video các bạn tải về ở cuối bài.IFrame

HÀM INDEX

Hàm INDEX sẽ trả về dữ liệu của một ô trong 1 vùng dựa trên chỉ số hàng và chỉ số cột của vùng đó. Về cơ bản, hàm INDEX có cú pháp như sau:

=INDEX(vùng_dữ_liệu, hàng_thứ_mấy, [cột_thứ_mấy])

  • vùng_dữ_liệu – là địa chỉ vùng dữ liệu chúng ta mong muốn “nhặt” ra 1 giá trị
  • hàng_thứ_mấy – ô cần quét dữ liệu nằm ở hàng thứ mấy?
  • cột_thứ_mấy – ô cần get dữ liệu nằm ở cột thứ mấy?

Để thử dùng hàm INDEX chúng ta đủ nội lực thử trực tiếp câu lệnh sau trên bảng tính phía dưới

=INDEX(A1:D10,6,3)

Hàm INDEX tìm dữ liệu trong vùng A1:D10 và trả lại dữ liệu trong loại thứ 6, cột thứ 3, tức là ô C6kết quả của câu lệnh INDEX trên sẽ làSeoul giống như trong ô B14

Xem thêm: Tổng hợp các cách sử dụng các hàm trong excel mới nhất 2020

HÀM MATCH

Hàm MATCH kiếm tìm 1 giá trị trong 1 vùng của bảng tính và đưa lại vị trí tương đối của ô chứa giá trị đó trong vùng tìm kiếm. Cú pháp của hàmMATCH như sau:

=MATCH(giá_trị_cần_tìm, mảng_tìm_kiếm, [kiểu_tìm_kiếm])

  • giá_trị_cần_tìm – trị giá cần được tìm kiếm vị trí trong mảng
  • mảng_tìm_kiếm – mảng chứa trị giá cần kiếm tìm
  • kiểu_tìm_kiếm – kiếm tìm trị giá chính xác hay kiếm tìm trị giá gần nhất.
    • 1 – hoặc bỏ qua: tìm giá trị lớn nhất mà trị giá đó bằng hoặc nhỏ hơn giá_trị_cần_tìmgiá trị trong mảng_tìm_kiếm cần được sắp đặt gia tăng dần
    • 0 – tìm trị giá trước nhất trong mảng bằng với giá_trị_cần_tìm. Đây là giá trị mà chúng ta sẽ rất hay sử dụng khi sử dụng hòa hợpINDEX/MATCH
    • -1 – tìm giá trị nhỏ nhất mà trị giá đó lơn hơn hoặc bằng giá_trị_cần_tìm

Khi đọc đến đây, chắc các bạn sẽ tự hỏi là Cái hàm MATCH này hình như k chức năng lắm thì phải. Đúng thế, nếu chỉ giống như vậy thì hàmMATCH k có nhiều chức năng lắm. Nhưng ở phần tiếp theo, khi hòa hợp cùng hàm INDEX thì chúng ta hãy xem xem cặp đôi này sử dụng gì được cho các bạn.

Sử dụng kết hợp INDEX VÀ MATCH TRONG EXCEL

Chúng ta sẽ đi làm ngay một gợi ý trước để các bạn có thể hiểu được sự kết hợp của hàm INDEX và MATCH trong Excel như thế nào. Chúng ta có bảng Excel sau đây:

Chúng ta muốn đi search xem nước nào có thủ đô là Seoul trong bảng này. bí quyết để tìm ra điều đó giống như sau:

=INDEX(B1:B10,MATCH("Seoul",C1:C10,0)

Trong mẹo này:

  • B1:B10 là cột chứa dữ liệu mà chúng ta muốn rà soátkiếm tìm hoặc trích lọc
  • MATCH("Seoul",C1:C10,0) sẽ cho chúng ta biết Seoul ở vị trí hàng thứ mấy ở trong bảng tính trên. kết quả Seoul ở vị trí hàng thứ 6 trong bảng tính. mẹo với INDEX trở thành: INDEX(B1:B10,6)
  • Khi phối hợp 2 phương pháp này lại và do tính chất của bảng tính, Seoul sẽ phải nằm cùng dạng với nước có thủ đô là Seoul nên ta đủ nội lực dùng phương pháp này để tìm ra được hiệu quả là Hàn Quốc

Tổng quát lại thì chúng ta có phương thức sau:

=INDEX( cột cần rà soát trị giá, (MATCH ( trị giá dùng để check, cột chứa giá trị này, 0 ))

Và một lần nữa, các bạn có thể thực hành INDEX và MATCH trong bảng tính Excel Trực tuyến phía trên. Khi các bạn đã chắc tay với INDEX và MATCH* chúng ta sẽ xem phần kế tiếp vì sao **INDEX và MATCH lại tốt hơn VLOOKUP ở nhiều trường hợp.

Tại sao INDEX VÀ MATCH tốt hơn VLOOKUP

VLOOKUP là cách thức 1 CHIỀU

Với VLOOKUP, chúng ta chỉ đủ nội lực tra cứu dữ liệu từ trái qua phải. Trong ví dụ tìm nước có thủ đô là Seoul ở trên, chúng ta sẽ k dùngVLOOKUP để đủ sức tìm ra Hàn Quốc được. Giống như các bạn đã thấy, INDEX và MATCH làm được điều này.

VLOOKUP sẽ bị sai nếu chúng ta thêm hoặc bớt cột ở trong bảng tính.

Cái này thì rõ ràng rồi, bởi vì khi sử dụng VLOOKUP các bạn phải chỉ ra cột nào chúng ta mong muốn quét trị giá về. Khi thêm hoặc bớt 1 cột ở giữa cột trước tiên và cột cần quét giá trị về thì cột cần quét trị giá bị xê dịch đi, dẫn đến kết quả của hàm VLOOKUP không đúng nữa. Với INDEX và MATCH thì điều này k xảy ra vì khi thêm/bớt cột thì công thức trong hàm INDEX và MATCH sẽ được điều chỉnh theo.

VLOOKUP sẽ gặp khó khăn khi kiểm tra 2 chiều

Các bạn có thể xây dựng Sheet2 trong bảng tính phía trên ra. Nếu chúng ta mong muốn rà soát dân số của Seoul vào năm 2001 thì trong trường hợp này chúng ta đủ sức dùng VLOOKUP – một cách chông gai, nhưng sử dụng INDEX và MATCH sẽ mau gọn hơn giống như sau:

=INDEX(A1:F10,MATCH("Seoul",C1:C10,0),MATCH("Năm 2001",A1:F1,0))

  • Hàm Match trước nhất sẽ cho chúng ta biết thủ đô Seoul của Hàn Quốc nằm ở định dạng thứ mấy
  • Hàm Match thứ hai sẽ cho chúng ta biết dữ liệu dân số của Năm 2001 nằm ở cột nào
  • Hàm Index sẽ dùng 2 thông tin trên và tìm ra dữ liệu chính xác cho chúng ta.

Xem thêm: Hướng dẫn chuyển số thành ngày tháng trong excel mới nhất 2020

Ứng dụng INDEX VÀ MATCH nâng cao

Dò tìm dữ liệu dựa trên nhiều cột điều kiện

Các bạn hãy xây dựng Sheet3 trong bảng tính sau đây:

Yêu cầu bài này là chúng ta phải điền vào D5:D16 dựa trên dữ liệu ở bảng testmẹo giống như sau:

=INDEX($F$5:$H$16,MATCH(1,(A5=$F$5:$F$16)*(B5=$G$5:$G$16),0),3)

  • Dấu {} – thể hiện rằng đây là một mẹo mảng, các bạn khi nhập công thức mảng cần dùng phím đặc biệt của Excel là CTRL + SHIFT + ENTER. Khi nhập cách thức vào Excel, các bạn nhập bình thường, k cần dấu {} và sử dụng phím CTRL + SHIFT + ENTER.
  • Phương pháp trên chưa được support trực tiếp trong phiên bản bây giờ của Excel Trực tuyến, nếu bạn mong muốn thử, đủ sức copy bảng tính này về Excel của bạn và thử cách thức đó.
  • Ảnh này để các bạn đủ nội lực hiểu rõ hơn hướng dẫn hoạt động của cách thức trên:

Sử dụng Index và Match trong Excel

  • Trong phương pháp trên, phần khó nhất là phần có hàm Match – MATCH(1,(A5=$F$5:$F$16)*(B5=$G$5:$G$16),0):
    • (A5=$F$5:$F$16) : Chúng ta đi so sánh trị giá của A5 với vùng $F$5:$F$16kết quả nhận được làFALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE
    • (B5=$G$5:$G$16) : Chúng ta đi so sánh giá trị của B5 với vùng $G$5:$G$16hiệu quả nhận được làFALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE
    • (A5=$F$5:$F$16)*(B5=$G$5:$G$16) : hiệu quả của phép tính này là0;1;0;0;0;0;0;0;0;0;0;0
    • cho nên Hàm Match của chúng ta từ MATCH(1,(A5=$F$5:$F$16)*(B5=$G$5:$G$16),0) trở thành:MATCH(1,0;1;0;0;0;0;0;0;0;0;0;0,0)kết quả của hàm match này là 2, dòng thứ 2 trong bảng check sẽ thoả mãn điều kiện Dan Brown và TáoTổng phải tìm là 271

Tra cứu các giá trị theo chiều dọc trong danh sách bằng cách sử dụng kết quả khớp xấp xỉ

Để thực hiện điều này, hãy dùng hàm VLOOKUP.

Quan trọng:  Đảm bảo các giá trị trong hàng đầu tiên đã được sắp xếp theo thứ tự tăng dần.

Ví dụ về công thức VLOOKUP đang tìm kiếm kết quả phù hợp gần đúng

Trong ví dụ trên, VLOOKUP sẽ tìm kiếm tên của học viên có 6 tardies trong phạm vi A2: B7. Không có mục nhập cho 6 tardies trong bảng, do đó, vlookup sẽ tìm kiếm đối sánh cao nhất tiếp theo thấp hơn 6, và tìm giá trị 5, được liên kết với Davetên, và do đó trả về Dave.

Tra cứu các giá trị theo chiều dọc trong danh sách kích cỡ không xác định bằng cách sử dụng kết quả khớp chính xác

Để thực hiện tác vụ này, hãy dùng hàm OFFSET và MATCH.

Lưu ý: Sử dụng phương pháp này khi dữ liệu của bạn nằm trong một phạm vi dữ liệu ngoài mà bạn làm mới mỗi ngày. Bạn biết giá là trong cột B, nhưng bạn không biết có bao nhiêu hàng dữ liệu mà máy chủ sẽ trả về và cột đầu tiên không được sắp xếp theo thứ tự bảng chữ cái.

Ví dụ về các hàm OFFSET và MATCH

C1 là các ô phía trên bên trái của phạm vi (còn được gọi là ô bắt đầu).

Khớp (“cam”, C2: C7, 0) sẽ tìm kiếm cam trong phạm vi C2: C7. Bạn không nên đưa vào ô bắt đầu trong phạm vi.

1 là số cột ở bên phải ô bắt đầu có giá trị trả về. Trong ví dụ của chúng tôi, giá trị trả về là từ cột D, doanh số.

Tra cứu các giá trị theo chiều ngang trong danh sách bằng cách sử dụng kết quả khớp chính xác

Để thực hiện tác vụ này, hãy dùng hàm HLOOKUP. Hãy xem ví dụ dưới đây:

Ví dụ về công thức HLOOKUP đang tìm kiếm kết quả khớp chính xácHLOOKUP sẽ tìm kiếm các cột bán hàng và trả về giá trị từ hàng 5 trong phạm vi đã xác định.

Tra cứu các giá trị theo chiều ngang trong một danh sách bằng cách sử dụng kết quả khớp xấp xỉ

Để thực hiện tác vụ này, hãy dùng hàm HLOOKUP.

Quan trọng:  Đảm bảo các giá trị trong hàng đầu tiên đã được sắp xếp theo thứ tự tăng dần.

Ví dụ về công thức HLOOKUP đang tìm kiếm kết quả khớp gần đúngTrong ví dụ trên, HLOOKUP sẽ tìm giá trị 11000 trong hàng 3 trong phạm vi đã xác định. Nó không tìm thấy 11000 và do đó sẽ hiển thị giá trị lớn nhất bên dưới 1100 và trả về 10543.

Tạo công thức tra cứu với trình hướng dẫn tra cứu

Trong _ z0z_,trình hướng dẫn tra cứu sẽ tạo ra công thức tra cứu dựa trên dữ liệu trang tính có nhãn hàng và cột. Trình hướng dẫn tra cứu sẽ giúp bạn tìm thấy các giá trị khác trong hàng khi bạn biết giá trị trong một cột và ngược lại. Trình hướng dẫn tra cứu sử dụng chỉ mục và khớp với các công thức mà nó tạo ra.

  1. Bấm vào một ô trong phạm vi.
  2. Trên tab công thức , trong nhóm giải pháp , bấm tra cứu.
  3. Nếu lệnh tra cứu không sẵn dùng, thì bạn cần phải tải trình hướng dẫn tra cứu _ trình _ chương trình.Làm thế nào để tải chương trình bổ trợ trình hướng dẫn tra cứu
  4. Bấm vào nút Microsoft Office_ z0z_,bấm tùy chọn Excel, rồi bấm vào thể loại bổ trợ.
  5. Trong hộp Quản lý, bấm Bổ trợ Excel, rồi bấm Đến.
  6. Trong hộp thoại bổ trợ sẵn dùng , hãy chọn hộp kiểm bên cạnh trình hướng dẫn tra cứu, rồi bấm OK.
  7. Làm theo các hướng dẫn trong trình hướng dẫn.

Xem thêm: Tổng hợp các kỹ năng dân văn phòng cần

Qua bài viết này, chắc các bạn cũng đã biết là ngoài VLOOKUP ra thì còn một mẹo khó sử dụng không giống nữa là INDEX và MATCH cũng khá là bổ ích khi mà gặp phải trường hợp VLOOKUP không xử lý được. Mong đây là bước bắt đầu tốt cho các bạn. Nếu bạn vừa mới đọc đến đây, cảm ơn bạn rất nhiều.

Lan Anh- tổng hợp

Nguồn: blog.hocexcel.online/support.office.com