CODEWITHSUNDEEP
excelindexingmatchvlookupexcel-indirect
Nate
Nate

Reputation: 161

Excel INDEX + MATCH (different worksheet with user defined function)

Solved, I made a "MATCH" mistake, thanks all!

I have uncertain number of worksheets every time with uncertain name, but my target worksheet to search is always the 3rd last one

so in VBA I did this UDF:

Function SHEETNAME() As String

    Dim L As Long
    L = ThisWorkbook.Worksheets.Count
    SHEETNAME = Sheets(L - 2).Name

End Function

therefore, calling =SHEETNAME() in my spreadsheet will always return the name of my target worksheet

then unfortunately, the exact value I need is 1 row below what I can reach with VLookup (3rd column)

=VLOOKUP($K$2,INDIRECT("'" & sheetname() & "'!"&"A2:X30"), 3, 0) <--- I need 1 row below this result

so I converted the formula to INDEX + MATCH

=INDEX(INDIRECT("'" & sheetname() & "'!" & "A2:X30"),MATCH($K$2,INDIRECT("'" & sheetname() & "'!" & "A2:A30"),0)+1,3)

somehow, the result of MATCH($K$2,INDIRECT("'" & sheetname() & "'!" & "A2:A30"),0) from F9 is N/A, why....

Upvotes: 0

Views: 247

Answers (1)

user11121185
user11121185

Reputation:

From A:X you want the third column's value in your VLOOKUP so it's INDEX(C:C, ...) or INDEX(A:X, ..., 3). The VLOOKUP always uses the first column for the lookup so it's going o be MATCH(K2, A:A, 0).

=INDEX(INDIRECT("'" & sheetname() & "'!A2:X30"), MATCH($K$2, INDIRECT("'" & sheetname() & "'!A2:A30"), 0)+1, 3)

Don't complicate INDIRECT strings with $. Nothing is going to change the string.

Upvotes: 1

Related Questions