Look for data based on 2 columns in Excel VBA

Question

As shown in the 2 images, there are 2 sheets. "Result" is the sheets I want the result to be in and the "From" sheet is the source to search from. Basically, I want to search for the names of that student based on the "class number" and "student number". Neither "class number" nor "student number" is unique which means there are possible duplicates. However, the combination of "class number" and "student number" is unique, which means each student would have a different "class number" and "student number" combination. So the approach I thought was to first create a supporting column that concats "class number" and "student number" and then do a VlookUp. The code is as follow:

Sub vlookupName()
    
    'get the last row of both sheets
    resultRow = Sheets("Result").[a1].CurrentRegion.Rows.Count
    fromRow = Sheets("From").[a1].CurrentRegion.Rows.Count
    
    'concat Class number and student number to get a unique string used for vlookup
    Sheets("Result").Range("D2:D" & resultRow) = "=B2 & C2"
    Sheets("From").Columns("A").Insert
    Sheets("From").Range("A2:A" & resultRow) = "=c2 & d2"
    
    'vlookup
    Sheets("Result").Range("A2:A" & resultRow) = Application.VLookup(Sheets("Result").Range("D2:D" & resultRow).Value, _
        Sheets("From").Range("a2:b" & fromRow).Value, 2, False)
        
    '(delete columns to get back to raw file for next test)
    Sheets("Result").Columns("D").Delete
    Sheets("From").Columns("A").Delete
    Sheets("Result").Range("A2:A" & resultRow) = ""
End Sub

Improvements of any part of the code or methods are appreciated.

Answer 1

Concatenating is dangerous when trying to lookup with multiple values. Consider the following 2 cases:

Class Number	Student Number
1	15
11	5

Both concatenations will result in 115 and that's simply not unique.

You could argue that adding a delimiter could fix that. Something like an underscore and the 2 examples above would become 1_15 and 11_5. Yes, that would work as long as your parts are numeric but what if they were texts? Something like:

Part 1	Part 2
1_	5
1	_5

Both concatenations will result in 1__5 and that's also not unique. Although this last example is forced I hope it proves the point that this approach is not clean and can lead to wrong results.

Based on the ranges shown in your 2 images, I would write the following formula in cell A2 of the Result sheet:

=INDEX(From!$A$2:$A$11,MATCH(1,INDEX((From!$B$2:$B$11=$B2)*(From!$C$2:$C$11=$C2),0),0))

or in a more english way:

=INDEX(ResultRange,MATCH(1,INDEX((KeyPart1Range=DesiredPart1)*(KeyPart2Range=DesiredPart2),0),0)) which can easily be extended by adding part3, part4 and so on to match as many criterias as needed.

The logic is simple:

1. Something like From!$B$2:$B$11=$B2 will return an array of boolean values (TRUE and FALSE) corresponding to the number of rows in the From!$B$2:$B$11 range
2. Multiplying two (or more) arrays of booleans will result in a single array of 1 and 0 where 1 means TRUE and 0 means FALSE
3. The INDEX(array,0) will return the full array without the need to press Ctrl+Shift+Enter (needed for Excel versions that are not Office 365)
4. The MATCH(1,...) will return the row index where all the specified criterias are met
5. The outmost INDEX returns the desired result

Why would you want to run VBA code to recreate a formula that can be done directly in Excel? It ussually "smells" of bad practice. The maintainability of the whole project is made much more difficult by such an approach. If you rename sheets, you will need to update code. If you change the ranges (insert a column for example), you will need to update code. And the list goes on and on.

Assuming that you don't want formulas in your final result tab then why not just create an intermediate sheet that does all the formulas (Excel formulas) you want and then your code could simply do a copy-paste to the final result tab where there will be just values. This way if you need to add extra logic, you can just work on the intermediate sheet in plain Excel and not worry about synchronizing any code.