I'm trying to automate royalty calculations, and have a spreadsheet with all the calculations for each author to determine how much is owed them. Till now we've input the quantity sold for each publication manually, but I've been trying to automate it, and, given my limited knowledge of Excel, keep getting errors.

I imported sales figures into a separate worksheet in the file that include the catalog number, description, dollar sales, and quantity sold. What I want to do is create a formula for quantity sold for each row that looks up the publication catalog number in column A, looks that number up on the imported data, and returns the value of quantity sold from column G of that worksheet row that matches the catalog number (or "0" if the value is not found, not false, because it then messes up the addition). I couldn't get Vlookup to work, which seemed the most likely prospect, and went around in circles with the list wizard. I also want the formula to be generic, not requiring that it include the catalog number to be looked up, since we have 700 publications.

David Schaap