asked on 4/5/2013

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 Applications Microsoft Office Microsoft Excel

Last Comment

Saqib Husain

4/6/2013

Saqib Husain

4/5/2013

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

4/5/2013

This is better

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

Lyn Udy

4/5/2013

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

Saqib Husain

4/5/2013

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

4/5/2013

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

4/5/2013

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

4/5/2013

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

4/6/2013

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.

See Pricing Options

Start Free Trial

Saqib Husain

4/6/2013

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