A          B          C       D

sam        22         sam        FORMULA
kevin      15         
harry      89         
ben        45
gary       35

I have the above employee list (employee name, employee ID). I need to create a formula in column D which shall give me the below result:

In column C, whichever cell I put sam, the adjacent cell (in column D) should give me the result as 22 (the item sam may repeat numerous times in the column, but still it should give the result)

  • Would you mind formatting your question more nicely? It is very difficult to read/understand. – BenjiWiebe Jun 14 '14 at 20:46
  • I assume you want this to work for the other employee names as well? – ben Jun 14 '14 at 21:40

A simple VLOOKUP should solve your problem. This is the formula for cell D1: =VLOOKUP(C1,A1:B5,2,FALSE). Adjust the first argument of this formula for the other cells in column D.

If you want to avoid the #N/A output for empty cells in column C use =IF(ISBLANK(C1),"",VLOOKUP(C1,A1:B5,2,FALSE)).


You're looking for a VLOOKUP function.

In D1, type this in:


I'll break this down:

  • =IFERROR This is so if the cell in column C is empty, you don't get an error

  • C2 This is the 'lookup_value' - what you are looking for

  • $A$2:$B$4 This is the 'table_array' - the range of the data you are searching through. The $ make this absolute, so you can fill the formula down to other cells

  • 2 This is the 'col_index_num' - the column in the range you set (ie. with a range of 2 columns, 2 is the last - or second - column)

  • FALSE This is the 'range_lookup' - 'FALSE' means look for an exact match.

  • 0 This is the output of the formula if the cell in column C is empty. You could also use "" for a blank string

You can edit this as required

This is an example with other employees as well (C4 is empty, so you get the value 0 in D4):

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.