Can I use lookup with multiple lookup values?

Question

I am working on a project that utilizes client and vendor data. Basically, once a client's contract is awarded to the vendor, I can enter a key (a 3 letter acronym of the vendor name) on the client sheet, that matches with a row of values describing the vendor. This key in turn populates the next 8 or so fields of the client sheet with data (mailing block, etc) pulled from the vendor sheet.

Currently, I am using lookup function on the client sheet (where AQ2 = vendor key, entered on Client!) with this formula (in different flavors):

=LOOKUP(AQ2, Vendor!A:A, Vendor!H:H)

This works, for one vendor; so I can send off the contract award letter and stuff like that to the one winner. However, is there is a way to send letters to all the people who don't win the contract?

So say 4 vendors bid, 3 need "decline" letters; but the 3 sets of information need to be managed from 1 sheet whose keys are Client! based just like the "award";

• How do I use lookup or another analogous function to import the information for a mail merge for multiple recipients based on a client key spreadsheet (Is it possible?) ? (This would be in addition to the initial 'award' lookup; thought not necessary at the same time.)

Answer 1

There may be a more "elegant" way of doing it, but if you are able to do the mail merge with one row pulled from the lookup function, you should be able to do it multiple times using some VBA to loop through the declined companies, filling their information into a worksheet, then calling a mail merge based on that.

I don't know VBA myself, but the logic would be something like:

for each row in Vendor!
    if row.VendorCode = Client!VendorCode, skip this record
    put row.VendorCode in DeclinedMailMerge!VendorCode
    do the mail merge on DeclinedMailMerge!
end

Answer 2

You should explore using an advanced filter macro. Set the criteria to exclude the awarded vendor such as <>ACME and the resultant returned data is your mail merge source. You can also use the same sheet for the award letter, with a different merge document. and have a client selector combobox to manage that selection and population of content for each type of letter.

The advanced filter, once you have it setup of is easily repeatable, programmable and likely quicker than a loop would be.