如何根据列和行选择从Excel表中选择特定值？

I have a table like below

我有一张如下表

I want to create a form where I either select or type in the model from the ones available and then based on that model I want to use an appropriate sX value from the table.

我想创建一个表单,我可以从可用的模型中选择或输入模型,然后根据该模型我想从表中使用适当的sX值。

For example, I need value s7, for model A20. It is 1.5. I want to use that value in another formula. How do I address that value?

例如,对于型号A20,我需要值s7。这是1.5。我想在另一个公式中使用该值。我该如何处理这个价值?

Ideally I want to be able to refer to the value by using the model and the s7, rather then the absolute Excel cell location. Data table is located on a separate sheet, just in case it matters.

理想情况下,我希望能够通过使用模型和s7来引用该值,而不是绝对的Excel单元格位置。数据表位于单独的表格中,以防万一。

2 个解决方案

#1

Use INDEX()

=INDEX($1:$1048676,MATCH("S7",$A:$A,0),MATCH("A20",$1:$1,0))

You can replace the "hard coded" values of "S7" and "A20" with cell refernces, thus allowing you to put the titles in those cells to return the proper value.

您可以使用单元格引用替换“S7”和“A20”的“硬编码”值,从而允许您将标题放在这些单元格中以返回正确的值。

#2

Here is the code which accepts the Model and the sX value to give you the output from the table,

这是接受Model和sX值的代码,为您提供表中的输出,

Sub searchTable()
Dim i As Long, j As Long
sx = InputBox("Enter sX value")
Model = InputBox("Enter the model")
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(i, 1) = sx Then
        Exit For
    End If
Next i
For j = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
    If Cells(1, j) = Model Then
        Exit For
    End If
Next j
'This is the value that you need, which can be used somewhere in your program
MsgBox Cells(i, j)
End Sub

#1