Lookup functions?

[Nev in Bolton]

As General Secretary for a local amateur theatre group I keep a main spreadsheet with a worksheet named "CONTACTS" with the names and addresses of people who buy tickets for our productions. This is used to send mailshots of our future shows. The columns are (A)Title/Name, (B)Surname, (C)Address 1, (D)Address 2, (E) Address 3, (F) Town/City, (G) Postcode. Inevitably I have several instances of people with the same surname and also people who live in the same road with the same postcode.

This spreadsheet also has a worksheet named "CURRENT SHOW" with the same columns of names and addresses of people who buy tickets over the phone or by post. Any new names appearing on this are copied to the "CONTACTS" worksheet for future mailshots. What I'm trying to do with this worksheet is to get it to refer to the "CONTACTS" worksheet to search for existing records, and enter the address in the appropriate columns if I enter the surname and postcode. I've worked out how to do it with a simple VLOOKUP function but of course I have problems when the same surname or postcode crops up more once. I've tried looking for a function which will do the job if I enter a surname and a postcode. Is it possible to do this? Any help will be greatly appreciated.

[rylo]

Hi

If you have multiple instances being returned for your selection criteria, how do you want the multiple results to be shown and how do you want to make your selection? Have a form? Or perhaps a dropdown selection option?

Put up a sample workbook with your structure and some sample data, and give some idea on what you would like to appear where so we can work with something concrete.

rylo

[Nev in Bolton]

Hello Rylo,

Thanks for your quick reply. I've attached a small sample file which is similar to the actual spreadsheet.

What I want to do is add a postcode and a surname into columns A & B in the "CURRENT SHOW" worksheet. I want Excel to check if that combination exists in the "CONTACTS" worksheet and then copy the rest of the address information across to the corrresponding columns in the "CURRENT SHOW" worksheet. In this worksheet I want any error messages suppressed such as #N/A to keep the appearance "clean"

=IF(ISERROR((VLOOKUP($A2,CONTACTS!$A$2:$G$20,2,0))),"",(VLOOKUP($A2,CONTACTS!$A$2:$G$20,2,FALSE))) will do it for just entering the postcode only, as you will see if you enter any postcode from the CONTACTS worksheet. But this will not differentiate between people with the same surname or the same postcode, hence the need to use "Postcode" and "Surname" as the search criteria. This function suppresses the #N/A resulting from a simple VLOOKUP function, however it returns a 0 if it encounters a blank cell.

I hope this helps,

Regards,

Nev

[rylo]

Nev

In 'current show'!C2 enter the formula
=IF(SUMPRODUCT(--(CONTACTS!$A$2:$A$20='CURRENT SHOW'!$A2),--(CONTACTS!$B$2:$B$20='CURRENT SHOW'!$B2),ROW(CONTACTS!$A$2:$A$20)),INDEX(CONTACTS!C:C,SUMPRODUCT(--(CONTACTS!$A$2:$A$20='CURRENT SHOW'!$A2),--(CONTACTS!$B$2:$B$20='CURRENT SHOW'!$B2),ROW(CONTACTS!$A$2:$A$20))),"")

Copy down / across as required.

Notes:
1) If there is not a unique combination of postcode / surname this will bring back a wrong result
2) If there is a blank cell in the source data, the output will be 0.

See if this starts you in the right direction.

rylo

[Nev in Bolton]

Strewth!! I'd never have worked that out in a month of Sundays!! Thank you, it works fine and I'm very grateful.

Zeros appearing in a row is not a problem because most names and addresses in the main spreadhseet have blank cells and any row with a zero in it will indicate that the name and address is already there. New names added will overwrite the formula and no zeros will appear. This will show that these need to be copied to the main spreadsheet. So it solves all problems. Brilliant!!! Thanks again!

Regards,

Nev