Search code examples
excelexcel-formulaworksheet-function

Vlookup not behaving as expected

A column      Bcolumn    Ccolumn      Dcolumn
Jack          A         John          =vlookup(A1,A1:C4,2)
Jason         B         John
John          C         Jason
                        Jack

When I press enter I see 

John - A
John - B
Jason - C

whatever is present in column B is being returned, what i want to do is get

John - C
John- C
Jason -B
Jack - A
Solution

  • Change to

    =VLOOKUP(C1,A1:B4,2)

    In fact, you better use absolute references, otherwise the lookup range will get messed up when you copy the formula down. 

    =VLOOKUP(C1,$A$1:$B$4,2)

    But really, I would advise dropping VLOOKUP in favour of INDEX and MATCH, which is way more flexible:

    =INDEX($B$1:$B$4,MATCH(C1,$A$1:$A$4,0))