I work for a charity, and want to analyse how many of our members rejoin each year. To do this I have created an Excel file with current and lapsed members' names in it, all 2412 of them - plus blank columns for each financial year, 2006-07, 07-08, 08-09, 09-10 and this year, 2010-11.
Another five worksheets have the names and dates they paid for that particular year, e.g. "06-07", where the first entry is Aberhart, Angela who joined on 1 May 2006.
I would like to put into sheet "customers" the date that she joined for each particular year, but can't figure out how to create the right formula.
In the customers sheet you need to include a VLOOKUP formula in each of the columns for match the member name to the date joined in the sheet for that particular year. You can use absolute (with $ signs) references to the look up ranges and offset and relative reference to the member name so that the formulas can be copied down the columns for all members.
In the VLOOKUP formula, use FALSE for the fourth option at the end to return exact matches (TRUE finds the closest match). In this case you may also want to wrap the formula in an IF function to filter out results where a match is not found.
