# Look up formula in Excel 2010 table

I am looking to generate a table (see ‘table’ sheet) which will lookup a wind direction (eg N, NE, WSW etc) together with a wind speed range (eg between 0 – 5 knts) on a separate sheet of data and count the number of times these paramters are met.  In other words on ‘Table’ sheet cell C5 formula to lookup the specified wind direction (N) in the table row(s) and the specified table column wind speed(s) (0 – 5, 5-10 etc) knots and return the number of occurrences for the given direction and speed ranges. attempts at using vlookup have failed.

In cell L5 on the same ‘Table’ sheet I am also looking to calculate the average speed for the various specified wind direction and combined speed ranges.

How might it be possible to dynamically change this table by selecting a specific time period such as year or month(s)?? to alter the various occurence values ?
Lookup-Table.xlsx
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Cost AccountantCommented:
The attached use of COUNTIFS may do the first job.

We may be looking at a second and third question for the other two, just to keep the diferent issues seperate.

NOTE: you are missing the column for 10-20
U--Lookup-Table.xlsx
Author Commented:
Steve, thanks  - COUNTIFS works a treat and cheers for noting obvious omission.

Let me know if need ask separate questions regarding other queries but feel relevant to this one workbook and wanted to be clear on what was required from outset in case maybe impact on how resolved.  The plots are already dynamic - with the workbook using name ranges.
Cost AccountantCommented:
I have added the formula for average using AVERAGEIFS.

I have added a second table to demonstrate the addition of date selection.
( I thought a second table may be clearer to see the changes made )
U--Lookup-Table.xlsx

Experts Exchange Solution brought to you by