VLOOKUP(lookup_value, table_array , col_index_num, [range_lookup])
First Column is the key, other columns/rows follow
- There is no atomic primary key, e.g. the worksheet may have columns (or rows) for Last Name and First Name of employees but not one where it is "<LastName>, <FirstName>"
- The worksheet is designed for presenting information in an order that makes sense to the users, not in an order that makes sense to a computer function.
- When there is a master worksheet, some look-ups may want to find the manager of a department and in others the department of the manager. If the department is to the left of (or above) the manager the second look-up cannot be done.
Work Around 1: Redundant columns/rows
- To the left of the Manager column insert a column, (NOTE: This puts Dept Name/Number into Column M). In the new column put a formula that make the new columns' values the same as column M, e.g. put "=M2" in the second row then copy-down for the rest of the rows.
- To the right of the Dept Name/Number column insert a column, (NOTE: This does not change the columns in question.). In the new column put a formula that make the new columns' values the same as column K, e.g. put "=K2" in the second row then copy-down for the rest of the rows.
Work Around 2: Normalize
Work Around 3: Concatenated Keys
A New Solution
Importance of CountIfs and SumIfs to V/HLookup
- Column/row order is not important. This was true with COUNTIF and SUMIF.
- COUNTIFS and SUMIFS can search multiple columns.
Add the Index ColumnThis solution calls for 1 new column. Column A is the indexing column for the VLOOKUP. Insert a column at A then put "=ROW()" in column A for all rows in the table. This single, simple column replaces all of the alternative key columns that might crop up in a large spreadsheet
Name the columnsPutting Names on the columns has some advantages. First, it makes the formulas more readable. Second, if the columns are shifted or rearranged the formulas continue to work.
- Index =People!$A:$A
- LastName =People!$B:$B
- FirstName =People!$C:$C
- HowMuch =People!$D:$D
- A - Last name
- B - First name
- C - VLOOKUP (How Much)
How it works
Building the VLOOKUP
- Starting point:
VLOOKUP(lookup_value, table_Array , col_index_num, [range_lookup])
- [range_lookup] = FALSE. False is selected because we want only exact matches.
VLOOKUP(lookup_value, table_Array , col_index_num, FALSE)
- Table_Array = Folks. Folks is the whole table, from Index in column A to the rightmost column, in this case column D.
VLOOKUP(lookup_value, Folks,Col_index_num , FALSE)
- Col_index_num = COLUMN(HowMuch). Column(HowMuch) returns the column number for the How Much column, which is the offset needed by the Lookup function. This is the primary reason for the limitations on table and Index placements.
VLOOKUP(lookup_value, Folks,COLUMN(HowMuch), FALSE)
- lookup_value = SUMIFS(Index,FirstName,B1,LastName,A1). By using SUMIFS the sum is the value in Index because only one row is found. (See below for handling this error. This assumes the combinations of FirstName and LastName are unique for each row.
VLOOKUP(SUMIFS(Index,FirstName,B1,LastName,A1), Folks,COLUMN(HowMuch), FALSE)
The Add-ons: Error handling
- The First Name and Last Name used in the SUMIF may not match any rows in Folks. To identify these, test with a COUNTIF that checks if there are no (zero) matching rows, e.g. IF(COUNTIFS(FirstName,A2,LastName,B2)=0,"Not one of ours!"
- The Folks table may have two rows with the same first and last name. To provide a message when this is encountered, use COUNTIFS to see if there are more than one IF(COUNTIFS(FirstName,A2,LastName,B2)>1,"Two people, same name"