Showing posts from February, 2012

Excel Spreadsheet Formulas for the Week

I'm in charge of the direct mailing at my office. Our leads come in via Excel sheets, which get fed through RoboAgent, and out pops an auto quote.

We recently had Database101 run our previous leads through their database to see if we could collect any phone numbers and/or emails.

We have 8 separate leads files, and 1 big amended file (with all the leads in no particular order). In order to get the quotes updated, I have to use the ReQuote feature, but wanted to update the individual leads files with phone/email. I opened the leads file, and copy/pasted the Amended file as Spreadsheet 2. So the Phone and Email columns were empty in Spreadsheet 1. Be sure that your columns are aligned! (ie if Phone column in Spreadsheet 1 is column C, then it better be C on Spreadsheet 2).  I used the following 2 links for the formula, just adjusting the columns to match and transplant:

=IF(ISNUMBER(MATCH($C7, Sheet2!$C:$C, 0)), INDEX(Sheet2!H:H, MATCH($C:$C, Sheet2!$C:$C, 0)), "")

where C is …