Vlookup or Index-Match

[ROKK]

How can you perform a vlookup with multiple variables?

i.e. I need to look up department and year and return a unique amount for both criteria.

The dataset will have department and year in their respective columns more than once.

 

[PaddyD]

On 2002-08-27 17:41, ROKK wrote:
How can you perform a vlookup with multiple variables?

i.e. I need to look up department and year and return a unique amount for both criteria.

The dataset will have department and year in their respective columns more than once.

Hi - welcome to the board. It's fairly straightforward to do multikey lookups...just search here for a few ideas. But, you say "The dataset will have department and year in their respective columns more than once" and that you want to "return a unique amount for both criteria". Aren't these mutually exclusive?

Care to post an example from your data & an indication of what results you'd expect. Consider downloading the html maker add in from the link below this post.

Paddy

 

[ROKK]

[No message]

 

[ROKK]

Dept. Year Amount
Accounting 1997 36.76
Accounting 1998 48.97
Operations 1997 56.98

If I wanted to perform a vlookup to return the amount for Accounting in 1997?

 

[Aladin Akyurek]

On 2002-08-28 08:41, ROKK wrote:

Dept. Year Amount
Accounting 1997 36.76
Accounting 1998 48.97
Operations 1997 56.98

If I wanted to perform a vlookup to return the amount for Accounting in 1997?

See my contrib in:

http://www.mrexcel.com/board/viewtopic.php?topic=16933&forum=2