VLOOKUP Confusion

jiwhite

I'm very confused about how VLOOKUP has been implemented.

• Can the search value reference a value in another column in the current row, or can it only be a string enclosed in quotes?  If it can only be a string, it makes the function pretty useless because I'd like to be able to lookup information based on data entered in a Form.
• Why does the column searched in the lookup table need to be the leftmost column?  Why isn't this a parameter to allow searching existing sheets that were created before the function was implemented or that may need to be searched in multiple ways? Can the lookup table reference a report, which could alter the order of columns?

Daniel Stein

Thanks for the questions.

• Yes, the search value can be a reference.
• While VLOOKUP requires the search term to be in the leftmost column, as an alternative you can use INDEX and MATCH to use any columns for your search and return values. I included information below about how to use them to perform lookups.
• You can use these functions to perform lookups on any sheets, including your existing sheets.
• Formulas can't reference data in reports at present.

Typical usage of INDEX and MATCH to perform lookups:

=INDEX(return_column, MATCH(search_value, lookup_column, 0))

• Summary: Looks up a value and returns a corresponding value from the same row but a different column (same as VLOOKUP)

• Arguments

  • return_column: The column of the value to return

  • search_value: The value to search for

  • lookup_column: The column in which to search