Vlookup - Trả về giá trị thứ N được tìm thấy

[tuthien1990]

Chào tất cả các anh/chị thầy/cô, hiện tại em có 1 vấn đề tuy nhỏ nhưng mà nó hok nhỏ xíu nào, mong anh/chị thấy/cô hướng dẫn giúp em với ạ.
Mặc định thì vlookup sẽ trả về giá trị đầu tiên nó tìm thấy được (có nhiều giá trị), nhưng em thì không muốn lấy giá trị đầu tiên, em muốn lấy giá trị thứ N mà vlookup tìm thấy (giá trị thứ 3 chẳng hạn). Như file đính kèm ạ.

 

[doatmenhhon]

Chào tất cả các anh/chị thầy/cô, hiện tại em có 1 vấn đề tuy nhỏ nhưng mà nó hok nhỏ xíu nào, mong anh/chị thấy/cô hướng dẫn giúp em với ạ.
Mặc định thì vlookup sẽ trả về giá trị đầu tiên nó tìm thấy được (có nhiều giá trị), nhưng em thì không muốn lấy giá trị đầu tiên, em muốn lấy giá trị thứ N mà vlookup tìm thấy (giá trị thứ 3 chẳng hạn). Như file đính kèm ạ.

Thay 3 bằng thứ tự cần tìm bạn mong muốn

 

[ChanhTQ@]

Sửa lại nội dung hàm sau khi có ý kiến của #4

Option Explicit
Function nVLOOKUP(Tri As Variant, CSDL As Range, Col As Byte, Optional Num As Byte = 1)
 Dim Cls As Range, sRng As Range
 Dim Dm As Byte, Rws As Long
 
 Rws = CSDL.Rows.Count + 1
 Set sRng = CSDL(1).Offset(-1).Resize(Rws).Find(Tri, , xlValues, xlWhole)
 If sRng Is Nothing Then
    nVLOOKUP = "Nothing"
 Else
    For Each Cls In sRng.Resize(Rws)
        If Cls.Value = Tri Then
            Dm = Dm + 1
            If Dm = Num Then
                nVLOOKUP = Cls.Offset(, Col - 1).Value
                Exit Function
            End If
        End If
    Next Cls
 End If
End Function

 

[phulien1902]

Code bài #3, giá trị A001=10 không tìm thấy.

 

[leoheocon]

Chào tất cả các anh/chị thầy/cô, hiện tại em có 1 vấn đề tuy nhỏ nhưng mà nó hok nhỏ xíu nào, mong anh/chị thấy/cô hướng dẫn giúp em với ạ.
Mặc định thì vlooup sẽ trả về giá trị đầu tiên nó tìm thấy được (có nhiều giá trị), nhưng em thì không muốn lấy giá trị đầu tiên, em muốn lấy giá trị thứ N mà vlookup tìm thấy (giá trị thứ 3 chẳng hạn). Như file đính kèm ạ.

Bạn
dùng thử CT này xem:

J8=SUMPRODUCT((COUNTIF(OFFSET(F7,,,ROW(1:5)),$F$7:$F$11)=[COLOR=#ff0000][B]5[/B][/COLOR])*$G$7:$G$11)

Chỗ màu đỏ là vị trí cần lấy!!!!

 

[tuthien1990]

Trước hết, em cảm ơn, và nó chạy ngon lành cành đào, nhưng có thể giúp em giải thích đoạn công thức không ạ.

 

[doveandrose]

Bạn
dùng thử CT này xem:

J8=SUMPRODUCT((COUNTIF(OFFSET(F7,,,ROW(1:5)),$F$7:$F$11)=[COLOR=#ff0000][B]5[/B][/COLOR])*$G$7:$G$11)

Chỗ màu đỏ là vị trí cần lấy!!!!

anh gì ơi , em thay F9 = A002 thì thấy kết quả nó ngộ ngộ sao ấy , với lại cột G hổng phải số mà là chữ thì em phải dùng công thức gì anh ?

 

[leoheocon]

anh gì ơi , em thay F9 = A002 thì thấy kết quả nó ngộ ngộ sao ấy , với lại cột G hổng phải số mà là chữ thì em phải dùng công thức gì anh ?

Em
quên mất điều kiện dò, em gửi lại CT, lúc đầu tính sài vlookup, sài sumproduct để khỏi Ctrl+Shift+Enter,:

=VLOOKUP(I8,IF(COUNTIF(OFFSET(F7,,,ROW(1:5)),$F$7:$F$11)=[COLOR=#ff0000][B]4[/B][/COLOR],$F$7:$G$11),2,0)

Ctrl+Shift+Enter!!!!

 

[Phuocam]

em muốn lấy giá trị thứ N

Bạn tham khảo ở bài 2: http://www.giaiphapexcel.com/forum/...-điểm-số-10-sang-sheet-DS&p=752740#post752740

Công thức dùng cho Excel < 2010:

=IFERROR(INDEX(G:G,SMALL(IF($F$7:$F$11=$I$8,ROW($F$7:$F$11),""),3)),"")

Kết thúc bằng Ctrl+Shift+Enter.

Công thức dùng cho Excel >= 2010:

=IFERROR(INDEX(G:G,AGGREGATE(15,6,ROW($F$7:$F$11)/($F$7:$F$11=$I$8),3)),"")

Thay số 3 bằng số bạn muốn.

 

[Phuocam]

Trùng .... .

 

[anhtuan2012]

Chào tất cả các anh/chị thầy/cô, hiện tại em có 1 vấn đề tuy nhỏ nhưng mà nó hok nhỏ xíu nào, mong anh/chị thấy/cô hướng dẫn giúp em với ạ.
Mặc định thì vlookup sẽ trả về giá trị đầu tiên nó tìm thấy được (có nhiều giá trị), nhưng em thì không muốn lấy giá trị đầu tiên, em muốn lấy giá trị thứ N mà vlookup tìm thấy (giá trị thứ 3 chẳng hạn). Như file đính kèm ạ.

=VLOOKUP(I8,OFFSET($F$6,MATCH(I8,F7:F11,)+I7-1,,10000,2),2,0) CTRL +shift +enter.

I7 là ô lựa chọn (1,2,3...)

 

[leoheocon]

=VLOOKUP(I8,OFFSET($F$6,MATCH(I8,F7:F11,)+I7-1,,10000,2),2,0) CTRL +shift +enter.

I7 là ô lựa chọn (1,2,3...)

Lỡ
dữ liệu sắp xếp lộn xộn thì sao bạn!!!!

 

[anhtuan2012]

Lỡ
dữ liệu sắp xếp lộn xộn thì sao bạn!!!!

Thế thì quất CT này:
=OFFSET(F6,SMALL(IF(F7:F11=I8,ROW(INDIRECT("1:"&ROWS(F7:F11))),""),I7),1)
CTRL+Shift+enter.

 

[anhtuan2012]

Thế thì quất CT này:
=OFFSET(F6,SMALL(IF(F7:F11=I8),ROW(INDIRECT("1:"&ROWS(F7:F11))),""),I7),1)
CTRL+Shift+enter.

Như này thì gọn hơn nè
=INDEX(G1:G11,SMALL(IF(F1:F11=I8,ROW(G1:G11)),I7),)
Crtl+shift+enter.

 

[tuthien1990]

=VLOOKUP(I8,OFFSET($F$6,MATCH(I8,F7:F11,)+I7-1,,10000,2),2,0) CTRL +shift +enter.

I7 là ô lựa chọn (1,2,3...)

Thanks anhtuan2012,nhưng mà em test với 3 4 5 thì nó không hoạt động (kết quả trả về #N/A)

 

[tuthien1990]

Như này thì gọn hơn nè
=INDEX(G1:G11,SMALL(IF(F1:F11=I8,ROW(G1:G11)),I7),)
Crtl+shift+enter.

Công thức này thì chạy ngon, nhưng có điều là
Thay thành như này nó không hoạt động anh ạ:
=INDEX(G1:G11,SMALL(IF(F1:F110000=I8,ROW(G1:G110000)),I7), )

 

[tuthien1990]

Thế thì quất CT này:
=OFFSET(F6,SMALL(IF(F7:F11=I8,ROW(INDIRECT("1:"&ROWS(F7:F11))),""),I7),1)
CTRL+Shift+enter.

Công thức này em gán nó vào xong Ctr + Alt + Enter mà nó trả về kết quả: #NAME?

 

[tuthien1990]

anh gì ơi , em thay F9 = A002 thì thấy kết quả nó ngộ ngộ sao ấy , với lại cột G hổng phải số mà là chữ thì em phải dùng công thức gì anh ?

Em cảm ơn, nhưng CT này em test thử so với đề bài của em thì OK, nhưng nó chưa tối ưu hóa khi thêm dữ liệu rộng ra.
Ví dụ: Khi em thêm 1 dòng giá trị A001 vào nữa (tức là thành : =SUMPRODUCT((COUNTIF(OFFSET(F7,,,ROW(1:5)),$F$7:$F$12)=5)*$G$7:$G$12)).
vậy thì phải sửa lại row như này mới đúng: (SUMPRODUCT((COUNTIF(OFFSET(F7,,,ROW(1:6)),$F$7:$F$11)=5)*$G$7:$G$11))
Nghĩa là em thêm 1 dòng thì cái row phải tăng lên 1 nó mới hoạt động. :|:|

 

[tuthien1990]

Thay 3 bằng thứ tự cần tìm bạn mong muốn

Cảm ơn anh doatmenhhon, đây là VBA (trích từ file đính kèm của anh) chạy ổn định nhất em đã test.

Function tim(Num, phamvi As Range, cot, tt As Long)
Dim r As Range[/PHP][PHP]Dim i As Long
Dim Kq As String
Kq = ""
For Each r In phamvi
    If r = Num Then
        i = i + 1
        If i = tt Then Kq = r.Offset(0, cot - 1)
    End If
Next r
tim = Kq
End Function

Sử dụng:

=tim(I8,F1:F10000,2,I7)

http://sv1.upsieutoc.com/2016/11/08/doatmenhhon.png

 

[anhtuan2012]

Công thức này thì chạy ngon, nhưng có điều là
Thay thành như này nó không hoạt động anh ạ:
=INDEX(G1:G11,SMALL(IF(F1:F110000=I8,ROW(G1:G110000)),I7), )

Bạn
phải mở thêm vùng cho Index nữa nhé!