How to pull out an exact Number/Date/Text

Blueprint 1

dfb_template1

Download the Excel file here

Syntax

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

=VLOOKUP(What you know,where it should be,what you want to know,how you want to know it)

  • What you know (lookup_value): it can be a number, text or date. Ex: Order ID, First name, Last name, full name, product ID, Quarter, Fiscal year, etc.
  • Where it should be (table_array): a table containing the left column with the information you know. The field should be one to one relationship (no duplicates). The source field should be in the same format as the lookup_value. E.g. searching numbers on a text column might not return matches
  • What you want to know (col_index_num): the column on which the information you want to know is located. The first column is 1.
  • How you want to know it ([range_lookup]): 0 if you want an exact match or 1 if you don’t care about having an  approximate match

Interactive tips

  • Let the user choose the input value (A2) from a drop-down list by using data validation
  • Let the user choose what value to return by making the col_index_num argument dynamic. Don’t hard code it in the formula, instead, place it in a cell. You can use an IF function to pair column descriptions with column numbers.
  • Let the user choose what source data to search on by making the table_array argument dynamic. Don’t hard code it in the formula, instead, create tables or named ranges and then allow the user to choose them in a cell. You can use an IF function to pair categories from a list to a dynamic named range formula
  • You can retrieve the column headers depending on the source data chosen

Resources


Blueprint 2