Excel get value based on multiple column condition

Question

Below is the table I have

Client Accuracy Utilization TAT Volume
ABC       1           2      3     4
XYZ       5           2      4     3
PQR       2           2      5     2

The output should be something like below

Client  Key Indicator
ABC       Accuracy
ABC       Utilization
XYZ       Utilization
PQR       Accuracy
PQR       Utilization
PQR       Volume

So for ratings less than 3 the client name and key indicator that has value less than 3 must get populated.

I tried using vlookup but the result is not as expected

Any insights how to achieve this.

Answer 1

Another Unpivot Flavor

Option Explicit

Sub UnPivot()
    
    Const sName As String = "Sheet1"
    Const sCriteria As Long = 3
    Const sOperator As String = "<"
    
    Const dName As String = "Sheet1"
    Const dFirst As String = "G1"
    Const dHeader As String = "Key Indicator"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Create a reference to the Source Range ('srg').
    Dim sws As Worksheet: Set sws = wb.Worksheets(sName)
    Dim srg As Range: Set srg = sws.Range("A1").CurrentRegion
    
    ' Validate the Source Rows Count ('srCount')
    ' and Columns Count ('scCount').
    Dim srCount As Long: srCount = srg.Rows.Count
    If srCount < 2 Then Exit Sub ' only column labels, no data
    Dim scCount As Long: scCount = srg.Columns.Count
    If scCount < 2 Then Exit Sub ' only row labels, no data
    
    ' Define the Source Array ('sData').
    Dim sData As Variant: sData = srg.Value
    
    ' Create a reference to the Source Values Range ('svrg').
    Dim svrg As Range
    Set svrg = srg.Resize(srCount - 1, scCount - 1).Offset(1, 1)
    
    ' Calculate the Destination Rows Count ('drCount').
    Dim drCount As Long
    drCount = Application.CountIf(svrg, sOperator & CStr(sCriteria)) + 1
    If drCount = 1 Then Exit Sub
    
    ' Define the Destination Array ('dData').
    Dim dData As Variant: ReDim dData(1 To drCount, 1 To 2)
    
    ' Write headers to the Destination Array.
    dData(1, 1) = sData(1, 1)
    dData(1, 2) = dHeader
    
    ' Declare variables.
    Dim r As Long
    Dim c As Long
    Dim n As Long
    
    ' Write the data (row labels, column labels) to the Destination Array.
    n = 1 ' because of headers
    For r = 2 To srCount
        For c = 2 To scCount
            If sData(r, c) < sCriteria Then
                n = n + 1
                dData(n, 1) = sData(r, 1)
                dData(n, 2) = sData(1, c)
            End If
        Next c
    Next r
    
    ' Write the values from the Destination Array
    ' to the Destination Range ('drg').
    Dim dws As Worksheet: Set dws = wb.Worksheets(dName)
    Dim dfCell As Range: Set dfCell = dws.Range(dFirst)
    Dim drg As Range: Set drg = dfCell.Resize(drCount, 2)
    drg.Value = dData
    
    ' Clear the contents of the Clear Range ('crg'), the range
    ' below the Destination Range.
    Dim crg As Range
    Set crg = drg.Resize(dws.Rows.Count - drg.Row - drCount + 1) _
        .Offset(drCount)
    crg.ClearContents
        
    ' Autofit the (entire) columns of the Destination Range.
    drg.EntireColumn.AutoFit
    
    ' Save the changes.
    wb.Save
    
End Sub

Answer 2

If one has Office 365 we can do:

=LET(
    clt,$A$2:$A$4,
    ind,$B$1:$E$1,
    rng,B2:E4,
    sq,COLUMNS(rng)*ROWS(rng),
    md,MOD(SEQUENCE(sq,,0),COLUMNS(rng))+1,
    it,INT(SEQUENCE(sq,,1,1/COLUMNS(rng))),
    FILTER(CHOOSE({1,2},INDEX(clt,it),INDEX(ind,,md)),INDEX(rng,it,md)<3,"")
    )

Without Office 365, PowerQuery or VBA will be the best to normalize and filter the data.