Related:

- Excel match two columns and output third
- Excel - Matching and sorting - How-To - Excel
- Excel match first 5 characters - How-To - Excel
- Compare values in two columns and return the value from third ✓ - Forum - Excel
- Excel Column Sort ✓ - Forum - Excel
- Excel MAtching Data ✓ - Forum - Excel

## 4 replies

Jimmy

Okay,

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.

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.

Sandy

I am having a similar issue, and am NOT good with functions....

I have a list of 5522 Codes which have their corresponding gender next to them. I have another list of 1046 codes that I would LIKE to have their genders next too. All 1046 codes are in the 5522 list of codes. How can I match the the codes in these two columns so I can have genders for teh 1046 codes?

ex. I have this...

Code Gender

12345 F

12346 M

12347 F

12348 F

12349 M

And this...

Code Gender

12345

12348

12349

How can I get the correct genders with the correct numbers in the second list?

I have a list of 5522 Codes which have their corresponding gender next to them. I have another list of 1046 codes that I would LIKE to have their genders next too. All 1046 codes are in the 5522 list of codes. How can I match the the codes in these two columns so I can have genders for teh 1046 codes?

ex. I have this...

Code Gender

12345 F

12346 M

12347 F

12348 F

12349 M

And this...

Code Gender

12345

12348

12349

How can I get the correct genders with the correct numbers in the second list?

rizvisa1

- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020

If your list 1 is in sheet 1 (Code in Column A and Gender in Column B) and your list 2 is in sheet 2 (Code in Column A and Gender in Column B), then on sheet 2 from cell B2 you can use this

=VLOOKUP(Sheet2!A2, Sheet1!A:B, 2, FALSE)

=VLOOKUP(Sheet2!A2, Sheet1!A:B, 2, FALSE)

I am looking to match information in one column with another and came accross your advice to joecrosstime.

I sent out faxes to over 800 different numbers and have them listed in an excell spreadsheet with names in A and fax numbers in B.

My fax service provider sent my message to each but 150 failed. I have a list of the failed numbers in C and need to match them to the cells in A and B so that I can follow on the faxes that failed.

How do I get the numbers in C to to match the numbers in B?

Thanks

syd

In Column D enter the function =INDEX($A$1:$A$800,MATCH($C1,$B$1:$B$800,0))... then just drag this down the cells to auto fill for the rest of the 150 rows.

What it is doing is Matching the value in C1 (bad number), with the values between A1 to A800 (original list of sent numbers). Once the MATCH function gets a match, it returns a row reference, which the INDEX function uses to give you the value in Column A (the name).

Hope this is what you were after?