tìm giá trị trong khoảng a-b

Xin chào ace,
Mong các anh chị giúp em công thức tại ô S6 để trả về giá trị (bậc 1 – bậc 5)
điều kiện là tìm theo dòng chứa cấp bậc (L1-L8) đã fix sẵn và điểm năng lực trong từng khoảng giữa các bậc
Em gửi file, trong file em có ví dụ ạ

fix ở đây là prepared/presented, tied in, corrected, hay unchangeable?

chú: fix chó/mèo có nghĩa là mang đi tuyệt giống.

công thức chắp vá, dùng tạm nhé
S6:

=IFERROR(INDEX($J$5:$N$5,,MATCH($R$6,INDEX($J$6:$N$19,MATCH($Q$6,$D$6:$D$19,0),N(IF(1,{1,2,3,4,5}))),1)),INDEX($J$5:$N$5,,MATCH(MIN(INDEX($J$6:$N$19,MATCH($Q$6,$D$6:$D$12,0),N(IF(1,{1,2,3,4,5})))),INDEX($J$6:$N$19,MATCH($Q$6,$D$6:$D$12,0),N(IF(1,{1,2,3,4,5}))),1)))

Thử làm 1 bảng không rõ đúng sai, chỉ thấy nó không báo lỗi, còn bảng dưới thì không biết làm. Tốt nhất nên nhập 1 bảng.

=INDEX($J$5:$N$5;0;MATCH(R6;OFFSET($B$5;MATCH(Q6;$D$6:$D$12;0);8;1;5);1))

Góp vui thêm 1 công thức.
Có 2 "L3" nên kết quả sẽ trả về "L3" đầu tiên tìm thấy.

="BẬC " & AGGREGATE(14,6,({1,2,3,4,5}*($D$6:$D$19=Q6)*($J$6:$N$19<=R6)),1)

Phận làm trai gõ phím bình thiên hạ,
Thân anh hùng click chuột định giang sơn

Thử làm 1 bảng không rõ đúng sai, chỉ thấy nó không báo lỗi, còn bảng dưới thì không biết làm. Tốt nhất nên nhập 1 bảng.
=INDEX($J$5:$N$5;0;MATCH(R6;OFFSET($B$5;MATCH(Q6;$D$6:$D$12;0);8;1;5);1))

Tạm ổn, nhưng tại sao có số 0 đỏ?

Vô tình thôi bác ạ, thường là em nhập 1 nhưng lẫn lộn qua lại với hàm OFFSET nên quên sửa, mà không lỗi nên em cũng không để ý.

Nhập thành 1 bảng nhưng trùng L3 thì cũng như không. Để chuẩn hóa thì phải chống trùng nhưng đặc thù nhân sự tiền lương nên bậc bị giống nhau.
Cải tiến 1 chút thì có thể thêm 1 cột nhóm như sau:

Lúc đó công thức là:

=INDEX($J$5:$N$5,1,MATCH(S6,OFFSET($B$5,MATCH(R6,IF(Q6="KT",$D$6:$D$12,$D$17:$D$19),0),8,1,5),1))

Cùng là L3 nhưng ra 2 kết quả khác nhau

Bạn Nhân viên B đúng phải là bậc 3 hoặc 4 mới đúng chứ chú nhỉ?

Đúng rồi, công thức thiếu 1 cái If
=INDEX($J$5:$N$5,1,MATCH(S7,OFFSET(IF(Q7="KT",$B$5,$B$16),MATCH(R7,IF(Q7="KT",$D$6:$D$12,$D$17:$D$19),0),8,1,5),1))

Thực ra bài này khi tra 1 bảng có nhiều cách như các công thức dài ngắn khác nhau bên trên, thường là tôi sẽ làm 2 Index và 2 Match chứ không làm Offset. Chỉ là đang gợi ý cho công thức hàm Offset bài 5: Đổi tham chiếu.

=INDEX($J$5:$N$5,1,MATCH(S6,INDEX($J$6:$N$12,MATCH(R6,$D$6:$D$12,0),0),1))

Nếu là 2 bảng, thì áp dụng phương pháp đổi tham chiếu như bài 8 thì sẽ là
=INDEX($J$5:$N$5,1,MATCH(S7,INDEX(IF(Q7="KT",$J$6:$N$12,$J$17:$N$19),MATCH(R7,IF(Q7="KT",$D$6:$D$12,$D$17:$D$19),0),0),1))

Mặc dù công thức dài hơn Offset nhưng không lệ thuộc vào B5, index ở đâu chỉ lệ thuộc vào đó, tư duy liền mạch.

Khóa học SprinGO phù hợp

Kỹ năng giải quyết vấn đề hiệu quả

Mô tả Nội dung Đánh giá Tài nguyên KỸ NĂNG GIẢI QUYẾT VẤN ĐỀ HIỆU QUẢHiểu đúng vấn đề là một nửa của giải...

Xem khóa học