Using Excel to return a value based on 3 variables

Question

I have a table that consists of multiple columns, and rows that contain the data. I want it to return a value based on multiply variables.

The headings are say: Client A, Client B, Client C, Client D, with each Client having two options, Opt1 Opt2, across the columns, with data held in rows below (A3-A100)

I want to write a formula that will return the value in the table based on the 3 chosen variables - e.g. Row E, Client B, Opt 2, returns the value in the corresponding cell

I have tried using INDEX Match but it's not working and wondering if a simple VBA code would be a better solution.

Thanks in anticipation.

Answer 1

It is possible, but messy, to do this in formulas if your reference array is sorted appropriately.

Otherwise a VBA function is indeed probably your best bet:

Function Match3(LookupZone As Range, _
            Seek1 As Variant, Seek2 As Variant, Seek3 As Variant, _
            RefCol1 As Long, RefCol2 As Long, RefCol3 As Long, _
            ReturnCol As Long) _
        As Variant

Dim ARow As Long

Match3 = CVErr(xlErrNA)
If RefCol1 > LookupZone.Columns.Count Then Exit Function
If RefCol2 > LookupZone.Columns.Count Then Exit Function
If RefCol3 > LookupZone.Columns.Count Then Exit Function
If ReturnCol > LookupZone.Columns.Count Then Exit Function
'                
For ARow = 1 To LookupZone.Rows.Count
    If LookupZone(ARow, RefCol1).Value = Seek1 _
        And LookupZone(ARow, RefCol2).Value = Seek2 _
        And LookupZone(ARow, RefCol3).Value = Seek3 _
            Then
        Match3 = LookupZone(ARow, ReturnCol).Value
        Exit Function
    End If
Next ARow

End Function

Then you can use this in your spreadsheet as required.