Avatar of Lyn Udy
Lyn Udy
 asked on

VLookup Function in Excel

Hello, I am building a table to monitor exercise activity and I need to use the vlookup function (or any other that might work) to automatically pull data from a table and automatically populate a cell. There are different activities, different intensities and different points. I have created a table showing the different activities with corresponding points depending on the intensity. I now want this point to be pulled once I select an activity from the activity column which happens to be a data validated column. The attached excel spreadsheet explains it better. I would appreciate help on this please.

Thanks
Activity-Table.xlsx
Microsoft ApplicationsMicrosoft OfficeMicrosoft Excel

Avatar of undefined
Last Comment
Saqib Husain

8/22/2022 - Mon
Saqib Husain

Enter this formula in C3 and copy it down and across

=VLOOKUP($B3,$I$3:$L$10,MATCH(C$2,$I$2:$L$2,0))
Saqib Husain

This is better

=IF($B3="","",VLOOKUP($B3,$I$3:$L$10,MATCH(C$2,$I$2:$L$2,0)))
Lyn Udy

ASKER
Hi ssaqibh, thanks for the possible solution, but it's not pulling correctly. Like If I select Running, it returns 9 which is not part of the options for Running.

Thanks
Your help has saved me hundreds of hours of internet surfing.
fblack61
Saqib Husain

Sorry change it to

=IF($B3="","",VLOOKUP($B3,$I$3:$L$10,MATCH(C$2,$I$2:$L$2,0),0))

and change the word Med to Medium from the lookup table
Lyn Udy

ASKER
Hi, this tends to work for only Running activity,. I get a return of #NA for the other activities. Also, I guess I'm not setting this up properly because It's something that should pull one result depending on the intensity of the activity not to fill the three cells at the same time. Do you think I should remove the data validation on the column and allow people to type in the intensity?
thanks for your help
Saqib Husain

The list in the lookup table has blank spaces at the end of some of the activity names. Get rid of those spaces and the formula should work.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Lyn Udy

ASKER
Hi, that seems to work, but it's not really serving the purpose. can we work on this new attached spreadsheet? The other one seem not to work exactly the way I want it to.
Thanks so much
Activity-Table.xlsx
ASKER CERTIFIED SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Saqib Husain

Oh, you have changed the sample file.

I do recommend the same sample file with a slight change in approach.

See attached
Copy-of-Activity-Table.xlsx