Lookup vba

[av8tordude]

Can someone assist with a lookup code that will lookup the county in column V for the city and state (column Q&R) and insert the finding in column S.

Thanks for you assistance

[Benzadeus]

Enter the array formula a tS11:
=INDEX($V$11:$V$508,(MATCH(1,($T$11:$T$508=R11)*($U$11:$U$508=Q11),0)))
(press Ctrl+Shift+Enter to enter it)
Then, copy and paste this formula to S12:S19

[av8tordude]

Thank Ben, but can this be done in vba code?

[Benzadeus]

Use the code below. Notice I extended the code in order to you understand this technique:
Sub ProcessData()
Dim sFormula As String
Dim sResult As String
Dim sDataCountry As String
Dim sDataState As String
Dim sDataCity As String

Dim l As Long
Dim rng As Range

sFormula = "=INDEX(^DataCountry^,(MATCH(1,(^DataState^=^State^)*(^DataCity^=^City^),0)) )"

sDataCountry = Range("V11:V508").Address
sDataState = Range("T11:T508").Address
sDataCity = Range("U11:U508").Address
sFormula = Replace(sFormula, "^DataCountry^", sDataCountry)
sFormula = Replace(sFormula, "^DataState^", sDataState)
sFormula = Replace(sFormula, "^DataCity^", sDataCity)

For l = 11 To 19
sResult = sFormula
Set rng = Range("S" & l)
sResult = Replace(sResult, "^State^", rng.Offset(, -1).Address)
sResult = Replace(sResult, "^City^", rng.Offset(, -2).Address)
Range("S" & l) = Evaluate(sResult)
Next l
End Sub

[Benzadeus]

You could use this faster method:
Sub ProcessData2()
Dim rng As Range
Set rng = Range("S11:S19")

rng(1).FormulaArray = "=INDEX(R11C22:R508C22,(MATCH(1,(R11C20:R508C20=RC[-1])*(R11C21:R508C21=RC[-2]),0)))"
rng(1).Copy Destination:=rng.Offset(1).Resize(rng.Rows.Count - 1)
'If you want to paste as values:
rng.Copy
rng.PasteSpecial xlPasteValues
End Sub

[av8tordude]

Thank you Ben. I notice that if no match is found, it inserts #N/A into the cell. How can I edit the code to leave it blank if no match is found?