Excel - Locate value in a list & copy it

Ask a question

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
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team