Address Information VLOOKUP

Hi All,

I am fairly new to the more technical side of EXCEL.

What I am trying accomplish is setting up a guest list that will automatically populate the state and postcode. We have had some customers that have entered incorrect details into these fields.

The issue that I have is that there are some locality name is in more than 1 state and therefore there is more than 1 post code.

for example the town "Raglan" there is one in NSW, VIC and QLD. I have tried a VLOOKUP however this is only populating the state with NSW and not identifying there is one in VIC and QLD

I would like to have the state collumn identify this and require the person to select the state from the dropdown list and then the postcode populate with the correct postcode.

I appreciate anyones help wth this!

Cheers,

Alan

Hello,

It would be much easier to tackle this the other way round, i.e. first enter the state, THEN enter the city name and THEN perform a lookup based on the state and city.

If you don't insist on a drop-down box for state and/or city, but would be content for the postcode to show up only if both cells have been filled in, then both of these formulas will do the trick.

=IF(COUNTA(D2:E2)=2,INDEX(AUSTPOST!B:B,MATCH(1,((AUSTPOST!C:C=E2)*(D2=AUSTPOST!A:A)),0)),"")

This is an array formula and has to be entered with CTRL-Shift-Enter.

or, an array free version

=IF(COUNTA(D7:E7)=2,INDEX(AUSTPOST!B:B,MATCH(E7&D7,INDEX(AUSTPOST!C:C&AUSTPOST!A:A,0),0)),0)

entered normally.

See attached for both formulas at work.