0
Thanks

A few words of thanks would be greatly appreciated.

Excel - Matching and sorting




Issue


Ok, how should a computer dummy to this (the dummy is me):
I have a list of 300 bike racers. In the columns next to the racer is items such as bib numbers, team, age, etc.


For example:
John Smith / #52 / Eagles Racing Team / 42 / San Francisco
When the race is over, the officials will give me a list of results in order by bib number, like this:
#47
#66
#67

What I'd like to do is enter those numbers in a column on the far left, next to a column which I've already set up so that it will look like this:
1 / #47
2 / #66
3 / #67

Then I'd like the computer to match the information for #47 with the rest of the information about #47 and down the list.
Do you know how I can do this?

Solution


To solve this you need to use a lookup table for example:

Say you have headings for the podium finishers in B1 Bib #, C1 Name, D1 Team, E1 Age, F1 Home Base
then you have A2, A3, and A4 showing 1st, 2nd, and 3rd and the podium finsishers listed in B2, B3, and B4 respectively.

Then your table of 300 bike racers data is in columns J2 through N302 in the same order as the podium finishes above. It is important to have the bib numbers first as this is what you will be using to search the data on.

The formula you will be using in cells C2 thru F4 for the podium finishers details will be:
=vlookup(lookup value, data table arrary, column index number,) 


where the lookup value will be the bib number in the above case b2, b3 and b4. The data table array will be J2:N302 and the column index number will be 2 for name, 3 for team, 4 for age and 5 for home base (keep in mind 1 is for the bib number you already have) then ensure you have a "," (comma) at the end before the close brackets.

For more information look up the help file for VLOOKUP tables.

Also just a thought, for better data accuracy instead of storing the age you could store their DOB then you wouldn't have to update the sheet for all riders each year.

Note


Thanks to Jimmy for this tip on the forum.
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Excel - Matching and sorting », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!