Vlookup problem

[phil_r]

I'm trying to get data out of a table (database), according 2 criteria.

Example:
Column1: location
Column2: month
Columuns: data1

Location Month data1
Geneva January 23
Paris January 34
Geneva February 26
Paris February 19

How can I get data1 corresponding to Location(x)-Month?

Typical Query: value of data1 in February in Geneva.
Expected outcome: 26

How can I do that?

Thanks in advance

 

[s-o-s]

On 2002-10-17 08:00, phil_r wrote:
I'm trying to get data out of a table (database), according 2 criteria.

Example:
Column1: location
Column2: month
Columuns: data1

Location Month data1
Geneva January 23
Paris January 34
Geneva February 26
Paris February 19

How can I get data1 corresponding to Location(x)-Month?

Typical Query: value of data1 in February in Geneva.
Expected outcome: 26

How can I do that?

Thanks in advance

If your data is in rows 2 - 11.

In A15 enter Location Name ie PARIS
In A16 Enter month January (May be wise to format Month as Text that way it will eliminate a problem with different days in a month)

A17 = SUMPRODUCT((A2:A11=A15)*(B2:B11=A16)*(B2:B11))



_________________
Hope This Helps.
Sean.<A HREF= "http://website.lineone.net/~s-o-s/Index.html">

Win98, XL2000

edit to change A16 to A17
This message was edited by s-o-s on 2002-10-17 08:13

 

[Andrew Poulsom]

This formula:

=SUMPRODUCT((A2:A5="Geneva")*(B2:B5="February"),C2:C5)

You can replace hard coded bits eg "Geneva" with cell references.

 

[phil_r]

Fantastic!!!
Thank you very much.
Regards
Phil