Excel - Locate value in a list & copy it

December 2016


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 :

This document entitled « Excel - Locate value in a list & copy it » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.