Need help with a Vlookup that will return an Average

Hello All,

I am having trouble nesting an average with a vlookup. I have a file that has a list of US zip codes that are linked to airports and then either an "outside" and/or "metro" area with pickup costs. I need to be able to create a lookup that will return the average cost for both Metro and/or Outside for each given station. I have created a lookup value that indicates the station name and whether or not it is outside - or - metro (EXAMPLE: LAXMetro). The problem is that when I use this function =AVERAGE(VLOOKUP(N2,J2:L713,3,FALSE)) I get back only the first instance of - for example again - LAXMetro and not the average of LAXMetro. What am I missing here? Please see attached file (this has been cut down to only show LAX for the purpose of upload as the actual file is far too big and inclusive of all US postal codes), my formula is in the table in columns N-Q. Thanks in advance for your help! I am using Excel 2010, if that will help you, help me, figure this out.

Regards

Kyle

Try this instead...
=AVERAGEIF($J$2:$J$713,N2,$L$2:$L$713)

Also, in your vlookup, you need to absolute the lookup range, or it will change as you copy it...
=VLOOKUP(N2,$J$2:$L$713,3,FALSE)

This looks a job to me for a pivot table.

See the attached file.