I have a table I need to return a single value from a row based on two variables. If the source of the variables was in a spreadsheet, I could do this with =INDEX(Col3, MATCH(var1 & var2, Col1 & Col2, 0)). My problem is that var1 and var2 are coming from a VBA form instead of cells in a worksheet. When I try to match on these variables in VBA using the same method, VBA is combining the separate strings into one and returning an error.
In the below table, the expected return would be Luke Skywalker when given Squadron = Red and Call Sign = Five. What I'm getting is my script trying to match RedFive instead which generates an error due to the one critera vs two ranges to search.
On the form:
TextBox1 = Sq.Value = Squadron
TextBox2 = CallSn.Value = Call Sign
Sheet1 is the data which is 49,750 lines in my original (not related to Star Wars) and the original data columns are not ordered as neatly as this example. This is imported data.
rw = WorksheetFunction.Match(Sq.Value & CallSn.Value, Worksheets(Sheet1).Range("A:A") & Worksheets(Sheet1).Range("B:B"), 0)
Pilot= Worksheets(Sheet1).Cells(rw, 3)
The result needs used in other processes in the script. (ie looking up the pilot's status on a different table)
Example Data:
| Squadron | Call Sign | Pilot |
|---|---|---|
| Red | Leader | Garven Dreis |
| Red | Two | Wedge Antilles |
| Red | Three | Biggs Darklighter |
| Red | Four | John D. Branon |
| Red | Five | Luke Skywalker |
| Red | Six | Jek Porkins |
| Red | Seven | Elyhek Rue |
| Red | Eight | Zal Dinnes |
| Red | Nine | Nozzo Naytaan |
| Red | Ten | Theron Nett |
| Red | Eleven | Ralo Surrel |
| Red | Twelve | Puck Naeco |
| Gold | Leader | Jon "Dutch" Vander |
| Gold | Two | Dex Tiree |
| Gold | Three | Evaan Verlainer |
| Gold | Four | Jake Farrell |
| Gold | Five | Davish Krail |
| Gold | Six | Brace Marko |
| Gold | Seven | Gazdo Woolcob |
| Gold | Eight | Datchi Creel |
This is what ended up working for me. I used a variant of Tim Williams (https://stackoverflow.com/users/478884/tim-williams) solution.
Issues I had with the original answer and my version of Office 365 were
Worksheets(Sheet1).Evaluate as an option. Because my inputs are coming from text boxes on forms, the data searched is imported, and the output of this block is being used further on in my code, this is what I ended up using...
Dim f As String
f = "=INDEX(C:C,MATCH(" & Chr(34) & Sq.Value & Chr(34) & Chr(32) & _
Chr(38) & Chr(32) & Chr(34) & CallSn.Value & Chr(34) & _
", 'Sheet1'!A:A" & Chr(32) & Chr(38) & Chr(32) & "'Sheet1'!B:B,0),1)"
'The worksheet version of Evaluate didn't exist in my version of Excel, so went with the VBA version.
Debug.Print Evaluate(f)