Motivation
VLOOKUP, short for "vertical lookup," is a function that you might already be familiar with from spreadsheet software like Microsoft Excel or Google Sheets. When working with multiple datasets, you might have one table with IDs and another with corresponding details. VLOOKUP helps merge these datasets by pulling in the related information from one table to another based on a common key. With Scispot, instead of sorting through rows manually, you can set up Labsheet columns that dynamically update and retrieve information as your data changes.
For example, imagine that you're running experiments in a wet lab and have two separate Labsheets:
Labsheet 1: Contains details about your experimental samples, such as sample IDs, the type of treatment each received (e.g., control or treated), and the date of collection.
Labsheet 2: Contains the results of various assays, such as the concentration of a particular protein measured from each sample, with the corresponding sample IDs.
Using a VLOOKUP-like approach, you can automatically match the sample IDs from Labsheet 1 with the assay results in Labsheet 2. This integration allows you to create a consolidated view of your data—so for every sample, you can see both its experimental condition and the measured protein level—in a single Labsheet.
LOOKUP in Labsheets
First and foremost, you will need both a source as well as a separate destination Labsheet to work with.
Source Labsheet: the source of the data you want to lookup and use
Destination Labsheet: where you want to perform the lookup, and display data from the source Labsheet
Your source Labsheet will not have any changes made to it, whereas the destination (where you are performing the lookup) will need a Link column added to it, along with Lookup columns that display data from the source Labsheet.
Link column: creates a links to specific rows on the source Labsheet, showing their IDs
Lookup column: read-only columns that show data from associated columns of the source Labsheet (e.g., a lookup column in the destination Labsheet might find and show the values taken from a number column in the source Labsheet)
Lookup columns currently support Text and Number data types, but we have plans to expand the available options in the future.
From your destination Labsheet, you will first need to add a Link column that points to the source Labsheet. This can be done by clicking the + (plus) icon on your destination Labsheet next to your last column, and selecting the Link column type. You will then need to select your source Labsheet from the dropdown menu, which can also be done by searching for its name.
To add Lookup columns, you can either add them while adding your Link column, or separately afterward:
Adding Lookup columns during Link column creation: Click the Manage Lookup Columns button and enable the source columns you want to show. Click Add Columns to add your Link column as well as the enabled Lookup columns to the Labsheet.
Adding Lookup columns individually: Click the Add Column button to add your Link column to the Labsheet. Then, click the + icon to add a new column. Choose the Lookup column type, select the lookup source (this refers to the name of the Link column you have created), and then select the desired source column you would like to bring data from. Click Add Column to add this Lookup column to your Labsheet.
Next, you will need to add rows from your source Labsheet to the Link column in order to populate your Lookup column(s). To do so, click the + icon on any empty cell of your Link column. From the menu that opens, you will see a preview of rows from the source Labsheet. Select the source Labsheet row that you would like to create a link to by double-clicking on it, and this will populate the Lookup columns, as well.
If your source Labsheet has many more than five entries, only the top five will be shown in the Link column selection. To add a specific row that is not shown, you can use the search bar to look for its ID value.
