# how to make a formula which will give the result as i mentioned in my message

``````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:

`=IFERROR(VLOOKUP(C5,\$A\$2:\$B\$4,2,FALSE), 0)`

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): 