Excel - Locate value in a list & copy it

February 2017


Issue


I have two lists:
  • List one contains two columns: ID numbers and age.
  • List two contains also two columns: ID (some from list 1) and their running time in a race.

I want to have in List one an addtional (third) column- RunTime with the matching runtime for the IDs. If ID in list one does not have a corresponding runtime in List two a "." value will be assigned.
List One . . . . . List Two
ID Age RunTime . . . . ID time  
11 17 . . . . . 29 10.3  
12 16 . . . . . 15 10.6  
13 20 . . . . . 22 11.9  
14 13 . . . . . 12 12.1  
15 15 . . . . . 26 14.5  
16 32 . . . . . 14 15.7  
17 15 . . . . . . .  
18 15 . . . . . . .  
19 47 . . . . . . .  
20 44 . . . . . . .  
21 32 . . . . . . .  
22 42 . . . . . . .  
23 15 . . . . . . .  
24 19 . . . . . . .  
25 14 . . . . . . .  
26 45 . . . . . . .  
27 45 . . . . . . .  
28 21 . . . . . . .  
29 44 . . . . . . .  
30 22 . . . . . . . 

Solution


You can use vlookup:
  • Lets say List 1 is between col A-C and list 2 is column D-E:
  • then in C2 you can have


=IF(ISERROR(VLOOKUP(A2, D:E, 2, false)), ".", VLOOKUP(A2, D:E, 2, false)) 

and just drag it down

Related


Published by aakai1056. Latest update on October 26, 2012 at 09:50 AM by Jeff.
This document, titled "Excel - Locate value in a list & copy it," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).