Calc / Excel trics

How to pick rows from a list

Example: columns A, B-X, where A holds keys (e.g. a list of logins), B holds a list of the needed keys, and C-X hold values (there can be several columns). The task is to select from columns A and B-X only the rows we need. Solution (not the best, but it works):

1. Make a new column Y with the formula: =IFERROR(VLOOKUP(A2;$B$2:$B$1000;1;);"--") — here 1000 = the number of rows, starting from the second one. 1 is the column number (1=B, 2=C, etc.) the value is taken from.
2. Now Y holds either a value from B,C,…X, or “–” for values that were not found.
3. You can sum up the values like this: =SUMIF(Y2:Y1000;"--";B1:B1000) — this is the sum of the NOT found ones (instead of B you can use any column you need). Now it can be subtracted from the total sum.