Gareth Thomas
New Member
What I want to do is look what is in one cell then post text from a look up table.
If I describe it's use I'm sure it will be clearer. It's for a mileage claim form for work.
Column 1 = Location
Column 2 = Postcode (Zipcode if American!)
I want to type in a location in column 1 then it uses a lookup from a table to automatically populate column 2 with a postcode that it has looked up from a table with all the locations and postcodes
So say for instance in the Table
LOOK UP TABLE
LOCATIONPOSTCODE
HOMEYO1 4WT
WORKYO2 3TZ
SHOPSYO3 6GH
STATIONYO2 2GT
if in column 1 I would type WORK how could I get column2 to look up the table below and put a value of YO2 3TZ
I know how to do simple IF formula and could quite happily apply that if the lookup range was only a few results like the above but I would actually want there to be about 100 results so it's not really feasible
Incidently if anyone really fancies a challenge at a previous company their mileage spreadsheet had a macro function whereby using a system similar to above it the spreadsheet would have a start postcode and an end postcode and then you could run the macro and it would somehow apply the postcodes behind the scenes to a mapping website like googlemaps, calculate the distance between the start and end points and then transfer these distances back onto the spreadsheet.
Thanks in advance if anyone can help me with the lookup table - not really worried about the autocalculate mileage - baby steps will do for now!
If I describe it's use I'm sure it will be clearer. It's for a mileage claim form for work.
Column 1 = Location
Column 2 = Postcode (Zipcode if American!)
I want to type in a location in column 1 then it uses a lookup from a table to automatically populate column 2 with a postcode that it has looked up from a table with all the locations and postcodes
So say for instance in the Table
LOOK UP TABLE
LOCATIONPOSTCODE
HOMEYO1 4WT
WORKYO2 3TZ
SHOPSYO3 6GH
STATIONYO2 2GT
if in column 1 I would type WORK how could I get column2 to look up the table below and put a value of YO2 3TZ
I know how to do simple IF formula and could quite happily apply that if the lookup range was only a few results like the above but I would actually want there to be about 100 results so it's not really feasible
Incidently if anyone really fancies a challenge at a previous company their mileage spreadsheet had a macro function whereby using a system similar to above it the spreadsheet would have a start postcode and an end postcode and then you could run the macro and it would somehow apply the postcodes behind the scenes to a mapping website like googlemaps, calculate the distance between the start and end points and then transfer these distances back onto the spreadsheet.
Thanks in advance if anyone can help me with the lookup table - not really worried about the autocalculate mileage - baby steps will do for now!