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

Last Comment
Saqib Husain
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

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
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

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?
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.
Lyn Udy

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
Patrick Matthews

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY