EXCEL公式或VBA基于另一个单元格数据提取数据

I have the following worksheet #1 (simplified for the question) of person and age:

我有以下工作表#1(简化问题)的人和年龄:

Person    Age
Bob       40
Brett     35
Brian     38

I would like to setup another worksheet where I have the following:

我想设置另一个工作表,我有以下内容:

Person    Age
Spot #1  FORMULA

The user will enter a name in Spot #1 and I want a formula/code in the cell titled FORMULA where the formula/VBA code will look at the table in worksheet #1 and pull in the appropriate age based on the person entered in .

用户将在Spot#1中输入一个名称,我想要一个名为FORMULA的单元格中的公式/代码,其中公式/ VBA代码将查看工作表#1中的表格,并根据输入的人数拉入适当的年龄。

What formula can I use to do this?

我可以使用什么公式来做到这一点?

Thanks for your help!

谢谢你的帮助!

2 个解决方案

#1

Use the VLOOKUP function. This example below assumes that your data table is in Sheet1!A1:B4. Although I would recommend that you make your data table a Named Range and reference it using VLOOKUP(A2, MyDataTable, 2)

使用VLOOKUP功能。下面的示例假设您的数据表位于Sheet1!A1:B4中。虽然我建议您将数据表设为命名范围并使用VLOOKUP(A2,MyDataTable,2)引用它

Person    Age
Spot #1   =VLOOKUP(A2, Sheet1!A1:B4, 2)

#2

try the vLookup function

尝试vLookup功能