# Obtain value in first column of table with multiple criteria

1. ## Obtain value in first column of table with multiple criteria

Dear kind Excel community,

Please see sheet attached. I wish to fill Cell L4.

L4 should provide me with a distance category value specific to the distance covered AND the position of the rugby player. For example, a Prop who has covered 15,500 metres should be in the MH (moderate-heavy) category, because the category goes from 15068m to 17307m.

If I was to change the position in Cell L2 to 'Centre', the category for 15,500m covered would become ML.

I have tried various Index-Match-Match formulas and found no joy.

Please may I have some help?

Many thanks

George

2. ## Re: Obtain value in first column of table with multiple criteria

Welcome to the excel forum

M2 =MATCH(L2,\$A\$2:\$I\$2,0)

after that

L4 =INDEX(\$A\$2:\$I\$9,MATCH(\$L\$3,INDIRECT(\$N\$2),1),1)

See the attached file.

3. ## Re: Obtain value in first column of table with multiple criteria

L4 should provide me with a distance category value specific to the distance covered AND the position of the rugby player
So, below would replicate your expected results for Prop (MH) and Centre (ML)

Formula:
`Please Login or Register  to view this content.`
in the above the INDEX/MATCH is used to isolate the distance values for the chosen position, the outer LOOKUP is used to isolate the category relative to those values.

Thank you!

5. ## Re: Obtain value in first column of table with multiple criteria

Thanks for the rep.

Glad I could help.

Thanks for marking the question solved.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1