How to connect multiple data sources in Smartsheet
There are two ways to connect multiple data sources in Smartsheet.
Vlookup
Index Match
1. VLOOKUP
This is an advanced Smartsheet function that looks up a value and returns a value from a different column in the corresponding row.
Syntax - VLOOKUP( search_value, lookup_table, column_num, [match_type ])
For example – In the sheet shown in the picture, if you want to look for an employee’s joining date based on the name, use the following formula –
=VLOOKUP([Metric / Search Value]@row, [Employee Name / Task]1:Department16, 2, false)
The above formula matches the employee’s name ([Metric / Search Value]) in the table highlighted in Green (Columns: Employee Name to Department, Rows: 1-16). Based on the match, the information from the 2nd column (Joining Date), is returned. The “false” match type means that we are looking for an exact match.
2. INDEX – MATCH
INDEX – This function returns an item based on the row and column index provided. Column index is optional. If it is not provided, the default value from the first column will be returned.
Syntax - INDEX(range, row_index, [ column_index ]) MATCH – This function returns the relative position/row number of a search value in a range/table.
Syntax - MATCH(search_value, range, [ search_type ])
MATCH function can be used within the INDEX function for a variable row index. This combination works the same as the VLOOKUP function.
For example – In the sheet shown in the picture, if you want to look for an employee’s joining date based on the name, use the following formula –
=INDEX([Joining Date]1:[Joining Date]16, MATCH([Metric / Search Value]@row, [Employee Name / Task]1:[Employee Name / Task]16, 0))
In the formula above, the final result will be picked from the Joining Date column based on the row number returned by the MATCH function. The MATCH function result will be the row number where the name specified in the Metric / Search Value matches the name in the Employee Name column.
In the attached picture, we are looking for Janice Roocroft’s joining date. The MATCH function will return row number 4 and the INDEX function will pick up the date from row 4 in the Joining Date column.
When to use INDEX-MATCH over VLOOKUP?
When the order of the columns is likely to be changed. Since VLOOKUP works with a specific column within a table range, rearranging columns would give the wrong results.
When the reference table has too many columns. In such a case, the more the amount of data being parsed, the processing time will be slower. Also, in the case of cross-sheet formulas, you may encounter cell link limits if the data range is too big. In the case of INDEX-MATCH, there are only 2 columns being referenced at any point in time, which avoids the above-mentioned issues.