vlookup is your friend
Sometimes you may find yourself trying to create a report of sufficient complexity that Salsa's report tool just can't handle it. If this ever happens, you can create two reports and combine them in Excel using the 'vlookup' function.
There are tons of primers on the internet, not to mention Microsoft's own documentation, that can tell you how to use the function. Consider this another one, but with a few Salsa-specific details.
What vlookup does:
Simply, vlookup looks for a value in the first column of a data array and, if it finds it, the function returns the value in the second (or any) column of that same row. Don't worry, this should make more sense after a simple example.
A Simple Example
I have two reports -- one with supporter names and one with donation information -- and I want to combine them. (Note that this is easily doable with the reports tool, so you wouldn't need to do this one in Excel.)
The essential thing is to have one column be the same in both reports.
With Salsa, I highly recommend using the _KEY fields, such as supporter_KEY and donation_KEY, since these are unique and specific identifiers. Using the KEYs drastically decreases the likelihood of combining wrong data. With this specific example, we're going to use the supporter KEY.
vlookup requires four pieces of information (or 'arguments') to work. While you can type in the formula directly in the cell where you want the new data to appear, I find it easier to use the "formula walkthrough," which you can access this way:
This will give you a new pop up called "Function Arguments," where you input what you're looking for and what you want returned. It also provides you some handy text in case you forget what each thing means.
Lookup_value: This is the value in the first report that you want to find in the second report. If you took my advice and used the KEY, that's what you'll want to use. In this case, I enter (or click on) 'A3' -- since I want to match Supporter_KEYs.
Table Array: This is the second report, or the data that you're returning. Generally, you could select the entire report, or only what's necessary depending on your needs. So, in this case, I'd enter 'A9:C11'. A few important notes on this step:
- If you're going to be copying the completed function into other cells (which I recommend, since you won't have to go through this process again), you'll want to make these values "absolute" instead of "relative." If you leave them relative (that is, the inputed array values vary with the location of the resulting cell), you risk missing important data. To make them absolute, put the $ symbol before each letter and number. This can be done quickly by hitting the F4 button on your keyboard.
- the value you're matching -- here, the supporter_key -- always needs to be in the first column of the table array.
Col_index_num: This is the number of the column from the table array (step 2) that you want to return. For this example, if I want to return the "amount," I'd enter '2' and if I wanted to return the date, I'd enter '3'.
Range_lookup: either 'true' or 'false'. If you enter 'false,' the matching values must match exactly. If 'true', they just need to be close. I almost always recommend using 'false' -- especially if you're using KEYs.
Entering all that gives you this formula:
And if you hit "OK", the formula does its thing and, in this case, returns '100.' Then you can copy the cell for the other supporters and return all the amount information. You can then do the same for transaction_date, but remember to keep the lookup value the same and to change the col_index_num to 3.
So here's what the final report now looks like, along with the formulas that returned each values for your reference:
A Final Note
The data returned by the vlookup function is dynamic, so if the information on the second report (the "table array") changes, the returned information will also change. If you delete the second report, you'll lose the returned data too. So, after you get this combined, I recommend copying the entire report and then using "Paste Special" > Values to 'lock' the returned values in place.