VLOOKUP Table Problem

Daywalker · 03-09-2008, 01:48 PM

I'm kicking myself at the moment, should be a simple task but cannot get it to work.

Basically I have a drop list of postcodes named as "Pcodes", and a database listsing employees and the postcode area they cover names "Data".

I've been trying using VLOOKUP to identify and list, how can I get the table to list each employee that covers the same postcode ?

I've attached a basic example of what I'm trying to do.

Cheers,

Daywalker

Postcode.zip

**Portuga** · 03-09-2008, 02:53 PM

Originally Posted by Daywalker

I'm kicking myself at the moment, should be a simple task but cannot get it to work.

Basically I have a drop list of postcodes named as "Pcodes", and a database listsing employees and the postcode area they cover names "Data".

I've been trying using VLOOKUP to identify and list, how can I get the table to list each employee that covers the same postcode ?

I've attached a basic example of what I'm trying to do.

Cheers,

Daywalker

Attachment 19704

The name of the person should be After the data you are looking for. (Post Code)

Insert a column before the name and put the codes there.In the formula insert the following:

=Vlookup($D$4;Database!$A2:$I27;2;FALSO)

Daywalker · 03-09-2008, 03:09 PM

Portuga,

You completely lost me on that one.

**Portuga** · 03-09-2008, 03:16 PM

Originally Posted by Daywalker

Portuga,

You completely lost me on that one.

1 Person Name can not be column a, it must be column B
2 Code Must be column a
3 Vlookup formula =Vlookup($D$4;Database!$A2:$I27;2;FALSE)

**davesexcel** · 03-09-2008, 03:18 PM

Originally Posted by Daywalker

Portuga,

You completely lost me on that one.

I believe the point is, Vlookup does not work backwards, the lookup criteria has to be to the left of the results,

I would suggest to try Auto-filter to get the results you want

http://www.contextures.com/xlautofilter01.html

Even check this example,of course may have to format your sheet differently for this to work properly
http://www.excelforum.com/attachment...8&d=1201836478

Daywalker · 03-09-2008, 03:24 PM

Ok, with you now.

Cheers for that guys.

**VBA Noob** · 03-09-2008, 03:25 PM

See if example helps

uses an array formula (Enter with Ctrl + Shift + Enter)

=IF(ISERROR(INDEX(Database!$A$1:$A$5,SMALL(IF(Database!B$1:B$5=Search!D$4,ROW(Database!B$1:B$5)),ROW(1:1)),1)),"",INDEX(Database!$A$1:$A$5,SMALL(IF(Database!B$1:B$5=Search!D$4,ROW(Database!B$1:B$5)),ROW(1:1)),1))

VBA Noob

Daywalker · 03-09-2008, 03:41 PM

VBA Noob,

Yip, see what your saying on this one, but some guys can have as many as 30 to 40 post codes to work around. So the table would be huge.

(But I'm keeping that idea, already know what I'm going to use it for)

I just searching just now to see if I can find a macro code to just look at the "Data" table, and if the search criteria say matched "EH1", then it would list all the employees names below.

Might be an easier option I think. So off to surf.