question regarding VLOOKUP

[Tarun2005]

Hi, I am trying to lookup a few values from a random database with formula =VLOOKUP(Sheet8!C2:C9855,Sheet7!A2:B6124,1,FALSE)

I am new to this and trying to understand where I made a mistake to get a spill error with result = #N/A

[#2]

@Tarun2005 

Using Sheet8!C2:C9855 as first argument means that you're looking up 9854 values, so the result requires 9854 free cells.

Also, why do you use 1 as column index into a two-column range? Don't you want to return a value from column B? If so, use 2 as column index.

[#3]

@Hans Vogelaar
Yes I am having 9854 free cells and
In sheet7:
the values I want to return are in A and the values I want to lookup are in B

[#4]

@Tarun2005 

Try

=IFERROR(VLOOKUP(Sheet8!C2:C9855,Sheet7!A2:B6124,2,FALSE),"")

If that does not work: could you attach a sample workbook to a reply, or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#5]

I used this:
=IFERROR(VLOOKUP(Sheet8!C2:C9855,Sheet7!A2:B6124,2,FALSE),"")
but I just get blank cells
and the file is in my office laptop so I probably cant share it

[#6]

@Tarun2005 

If you get blanks. that means that none of the cells in Sheet8!C2:C9855 have an exact match in any of the cells of Sheet7!A2:A6124

[#7]

@Tarun2005 

The good news is that, since you are getting a #SPILL! error, you must be using Excel 365 (or 2021), so you need never use the obsolete VLOOKUP again!  The spill error arises because Excel is trying to give you a result for every lookup value in your formula.  The newer function XLOOKUP will do the same but is more explicit concerning what is being searched and what is being returned.  The formula

=XLOOKUP(value,array1,array1,"(missing)")

will return an array that looks very much like the lookup 'value' array, but with the word "missing" in place to values that could not be found.

[Note: 'value' and 'array1' are defined names I have applied to the single column arrays.]