Having some issue with vlookup-CodePudding

I'm testing the VLOOKUP function with different sheets. I have created a sheet1 as my data reference table and sheet2 as where data is supposed to be filled with reference to the Name. I just don't know what is wrong with the formula. It keeps giving the #ref!.

Below is the formula that I'm using.

=VLOOKUP(A2,Sheet1!$A$2:$A$6,2,FALSE)

SHEET1

SHEET2

CodePudding user response：

The 2nd argument should include the ID column from Sheet1. Like below:

=VLOOKUP(A2,Sheet1!$A$2:$B$6,2,FALSE)

The way Vlookup works is - it looks for the value of A2 in the first column in $A$2:$B$6 and returns the 2nd column with Exact Match (FALSE)

So if you change the 3rd argument(2) to 3, then the range should have a minimum of 3 columns like $A$2:$C$6

CodePudding user response：

You can try change argument 4th to 0 and change $A$2:$A$6 to $A$2:$B$6

=VLOOKUP(A2,Sheet1!$A$2:$B$6,2,0)

CodePudding user response：

#REf occurs when your reference is not valid .See ,You referenced $A$2:$A$6,It is not valid ,As your array in sheet1 is extended to B6 . Use this:=VLOOKUP(A2,Sheet1!$A$2:$B$6,2,FALSE)