Excel中基于偏移与逻辑测试的MRQ查找及条件输出问题

Hey there! Let's work through this problem together. You're looking to locate the text "MRQ" in your Excel sheet, jump down one row, then compare that row's product value against a benchmark to output "Over" or "Under"—and you're stuck adding a second conditional check. Let's break this down with both formula and VBA solutions, plus examples of how to expand with extra conditions.

First, Let's Clarify the Dataset Example #

Let's assume your sheet looks something like this (adjust column/row references to match your actual data):

Option 1: Worksheet Formula (No VBA Needed) #

If you prefer using a formula instead of code, you can use INDEX + MATCH to find the row after "MRQ", then compare the product value to your benchmark.

Basic Formula: #

=IF(INDEX(B:B, MATCH("MRQ", A:A, 0)+1) > $B$4, "Over", "Under")

How it works: #

1. MATCH("MRQ", A:A, 0) finds the row number where "MRQ" first appears in column A.
2. Adding +1 shifts us down one row to target the product value.
3. INDEX(B:B, ...) pulls the product value from column B at that shifted row.
4. The IF statement compares this product value to the benchmark (in cell B4 here) and outputs "Over" or "Under".

Adding a Second Condition (e.g., Equal to Benchmark) #

If you want to handle cases where the product matches the benchmark, extend the formula with a nested IF or IFS:

=IFS(INDEX(B:B, MATCH("MRQ", A:A, 0)+1) > $B$4, "Over", INDEX(B:B, MATCH("MRQ", A:A, 0)+1) = $B$4, "Equal", TRUE, "Under")

Option 2: VBA Code (For More Control) #

If you're working with VBA and struggling to add a second conditional check, here's a robust solution that handles single or multiple "MRQ" instances, plus extra conditions.

Basic VBA Code (Single "MRQ" Check) #

Sub CheckProductAgainstBenchmark()
    Dim mrqCell As Range
    Dim productVal As Double
    Dim benchmarkVal As Double
    Dim resultCell As Range
    
    ' Locate the first cell with "MRQ"
    Set mrqCell = Cells.Find(What:="MRQ", LookIn:=xlValues, LookAt:=xlWhole)
    
    If Not mrqCell Is Nothing Then
        ' Get product value from the row below MRQ (same column)
        productVal = mrqCell.Offset(1, 0).Value
        
        ' Define your benchmark location (adjust this to your actual cell)
        benchmarkVal = Range("B4").Value
        
        ' Set where to output the result (next to the product value here)
        Set resultCell = mrqCell.Offset(1, 1)
        
        ' Primary comparison + second condition example
        If productVal > benchmarkVal Then
            resultCell.Value = "Over"
        ElseIf productVal = benchmarkVal Then ' This is your second condition
            resultCell.Value = "Equal"
        Else
            resultCell.Value = "Under"
        End If
    Else
        MsgBox "Couldn't find 'MRQ' in the worksheet!"
    End If
End Sub

Extended VBA Code (Multiple "MRQ" Instances) #

If your sheet has multiple "MRQ" entries and you need to check all of them:

Sub CheckAllMRQInstances()
    Dim mrqCell As Range
    Dim productVal As Variant
    Dim benchmarkVal As Double
    Dim firstFoundAddress As String
    
    benchmarkVal = Range("B4").Value ' Adjust benchmark cell
    
    ' Find first "MRQ"
    Set mrqCell = Cells.Find(What:="MRQ", LookIn:=xlValues, LookAt:=xlWhole)
    
    If Not mrqCell Is Nothing Then
        firstFoundAddress = mrqCell.Address
        Do
            productVal = mrqCell.Offset(1, 0).Value
            
            ' Add a check for valid numeric product values
            If IsNumeric(productVal) Then
                ' Multiple conditions with cleaner readability
                Select Case productVal
                    Case Is > benchmarkVal
                        mrqCell.Offset(1, 1).Value = "Over"
                    Case Is = benchmarkVal
                        mrqCell.Offset(1, 1).Value = "Equal"
                    Case Else
                        mrqCell.Offset(1, 1).Value = "Under"
                End Select
            Else
                mrqCell.Offset(1, 1).Value = "Invalid Value"
            End If
            
            ' Find next "MRQ"
            Set mrqCell = Cells.FindNext(mrqCell)
        Loop While Not mrqCell Is Nothing And mrqCell.Address <> firstFoundAddress
    Else
        MsgBox "No 'MRQ' entries found!"
    End If
End Sub

Key Notes for Adding Conditions: #

• Use ElseIf for sequential conditions, or Select Case for cleaner readability when there are multiple checks.
• Always validate that the product value is numeric before comparing (avoids errors if the cell is empty or has text).

内容的提问来源于stack exchange，提问作者BRolf