如何通过用户输入编号在Excel/Google Sheets中引用单元格

Hey there! Let's break down how to set up this experience calculation for your game level table—since you're already a mid-level Excel user, this should be straightforward. I'll cover both formula-based solutions (works for Excel and Google Sheets) and a VBA option if you want more automation in Excel.

Formula-Based Solution (Cross-Platform: Excel + Google Sheets) #

First, let's define your input cells to keep things clean:

• Enter your current level in cell D1
• Enter your target level in cell D2
• The result (required experience) will go in D3

Using XLOOKUP (Recommended for Excel 365/Google Sheets) #

XLOOKUP is intuitive and handles exact matches easily. Use this formula in D3:

=XLOOKUP(D2, A:A, B:B) - XLOOKUP(D1, A:A, B:B)

How it works:

1. XLOOKUP(D2, A:A, B:B) pulls the total experience needed to reach your target level
2. XLOOKUP(D1, A:A, B:B) grabs the total experience for your current level
3. Subtract the two values to get the exact experience needed to level up

To handle invalid level inputs (like a level not listed in column A), wrap it in IFERROR:

=IFERROR(XLOOKUP(D2, A:A, B:B) - XLOOKUP(D1, A:A, B:B), "请输入有效的等级")

For Older Excel Versions (No XLOOKUP) #

If you're on an older Excel version without XLOOKUP support, use VLOOKUP instead:

=VLOOKUP(D2, A:B, 2, FALSE) - VLOOKUP(D1, A:B, 2, FALSE)

Add error handling the same way:

=IFERROR(VLOOKUP(D2, A:B, 2, FALSE) - VLOOKUP(D1, A:B, 2, FALSE), "请输入有效的等级")

VBA Solution (Excel Only) #

If you want a more automated experience—like having the result update instantly when you change the current/target level—you can use a worksheet event macro.

Here's how to set it up:

1. Right-click your worksheet tab (e.g., "Sheet1") and select View Code
2. Paste this code into the VBA editor:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Only trigger if D1 or D2 are modified
    If Not Intersect(Target, Me.Range("D1:D2")) Is Nothing Then
        Dim currentLvl As Integer, targetLvl As Integer
        Dim currentExp As Long, targetExp As Long
        
        ' Get input values
        currentLvl = Me.Range("D1").Value
        targetLvl = Me.Range("D2").Value
        
        ' Look up corresponding experience values
        currentExp = Application.VLookup(currentLvl, Me.Range("A:B"), 2, False)
        targetExp = Application.VLookup(targetLvl, Me.Range("A:B"), 2, False)
        
        ' Calculate and display result, handle errors
        If Not IsError(currentExp) And Not IsError(targetExp) Then
            Me.Range("D3").Value = targetExp - currentExp
        Else
            Me.Range("D3").Value = "无效等级"
        End If
    End If
End Sub

3. Close the VBA editor and save your workbook as a .xlsm (macro-enabled) file

This macro will automatically update the result in D3 every time you adjust the values in D1 or D2, and it will show "无效等级" if either level isn't found in your table.

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