Help with non regular Lookup query

Good day,
I would like some assistance. Please see the attached spreadsheet.

Open VBA, look at the Glass form, on the Roof tab. There I have a roof type combo box where you can select the two values 'Metal Sheeting ' and 'Clay tiles'.
now have a look at the spreadsheet, the 'tables' sheet

When the "metal sheeting" is selected, table 8 is applicable, and if Clay Tiles are selected, Table 9 is applicable.

Now, for example, when "Metal sheeting" is selected, it must look at the climate zone value (showclimatezone TextBox), and according to that values it must return the R values to the textboxes on the Glass form (Roof tab) textboxes already provided for.

A working example would be:
a 'Sheet metal' roof in Zone 3 will return the value of cell D59 to RoofRV, D60 to CeilingRV and D61 to InsRV

Any help will be appreciated!

Cheers,

JakesFenestration CalculatorV1.1.xlsm

Hi!
This should work for you.
I've tested it in your workbook.
Insert this in the userform code.

Private Sub RooftypeBox_Change()
Dim M As Range
Dim C As Range
Set M = Worksheets("tables").Range("A58:G58").Find(showclimatezone, Lookat:=xlWhole)
Set C = Worksheets("tables").Range("A64:G64").Find(showclimatezone, Lookat:=xlWhole)
RoofV = RooftypeBox.Value
Value1 = "Metal Sheeting"
Value2 = "Clay Tiles"
If RoofV = Value1 Then
    RoofRV = M.Offset(1, 0)
    CeilingRV = M.Offset(2, 0)
End If
If RoofV = Value2 Then
     RoofRV = C.Offset(1, 0)
     CeilingRV = C.Offset(2, 0)
End If
End Sub

Hi there,

Thanks a million!!!

You can also add if you need (don't know)

If showclimatezone Is Nothing Then
MsgBox "The Climate Zone Is Empty, Please Enter The Climate Zone"
End If