When using VLOOKUP why is a range needed at all?-CodePudding

I'm having difficulty learning the VLOOKUP function in spreadsheets. Maybe I'm over thinking things, or not grasping the function properly?

Here's what I understand the basic syntax for the VLOOKUP function to be: =VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, Approximate match (TRUE) or Exact match (FALSE)).

I don't understand why the range is necessary for this Function to work. With the lookup value I'm already identifying which field I want to find the corresponding data for, and with the column number I'm identifying exactly which corresponding column I want the data pulled from. Why is a range needed at all?

CodePudding user response：

Disclaimer, I have no idea if this is really how it works in the code.

Opinion: It separates the range from the rest of the sheet.

Remember that excel allows you to copy paste your results. If the range is not defined as you move it to a different location, it can't know for sure where to stop when searching, and if your "column number" is the same as the "column letter" it was pasted unto.

If your columns are "name, email, age", and your column is "2" for email (column B), inserting a column to the left (example, to add an id number), it'd look at name (column B), which something Excel should be able to fix when range is given

Adding further, that a range also allows you to have multiple tables stacked unto the same column, like if someone is a certain gender, VLOOKUP can be used to set certain expected thing, like titles, gender specific questions, and so on. Making a column in your sheet containing limited to a few answers looks bad, especially when you also have multiple short lists that do use VLOOKUP too (Civil Status, Education level, Districts within a city, etc)

CodePudding user response：

Excel files are spreadsheets, not databases with tables. Excel files can be used as databases with tables, but that's not the basic idea. Let me give you an example:

You say: I would like to find the value 5 in a column, something like:

   A    B
==== ====
   1    a
   2    b
   3    c
   4    d
   5    e
   6    f

So you want to do VLookup(5,1).

However, what if a person makes the following spreadsheet

    A     B
=====  ====
Info1
    1     a
    2     b
    3     c

Info2
    4     d
    5     e

Here, you have two matrices: the one, containing information about 1, 2 and 3, and the other, containing information about 4 and 5.

The correct result of VLookup(5,Info1) should be #NA (error value, because 5 does not belong to Info1), while your proposal would yield another result, which is not the desired behaviour.