EXCEL Formula or VBA To Extract Data Based on Another Cell's Data

Question

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

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 .

What formula can I use to do this?

Thanks for your help!

Answer 1

try the vLookup function

Answer 2

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)

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