For example, you have the following data in worksheet called "Sheet1", at range A1...B4:
Country1 | 10 | Country2 | 14 | Country3 | 12 | Country4 | 20 |
Then you can use the next example:
Dim country As String country = "Country2" Dim VAT As Double VAT = -1 On Error Resume Next VAT = Application.WorksheetFunction.VLookup(country, Sheet1.Range("A1:B4"), 2, False) On Error GoTo 0 MsgBox VAT
It returns the value of the second column based on country variable, or -1 if the country is not found.
Adjust it for your case. Show more real details if it does not work.
Do get a value from the E7 cell you can do this: country = Sheet1.Range("E7").Value.
Using the VLOOKUP and other functions, sometimes it is also possible to write formulae without VBA macros: https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1.