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

Question

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)

Answer 1

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

Answer 2

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