multi-criteria retrievals

[hoosier41]

Hi,

I'm wondering if anyone can help me figure out what I have wrong in the
following formula? I am trying to search one worksheet for the rows that
meet these three criteria: SDS PM, Global Delivery Lead and 6, and return the
name of the event(located in G6:g40) associated with that row to another
worksheet in the same workbook.

=INDEX('Master Com Plan'!G6:G40,MATCH(1,('Master Com Plan'!A6:A40="SDS
PM")*('Master Com Plan'!H6:K40="Global Delivery Lead")*('Master Com
Plan'!Q6:Q40=6),0))

 

[Bob Phillips]

The problem is the range for testing against the Global Delivery Lead, it
spans 4 columns, it should be H6:H40 or K6:K40

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[hoosier41]

Thanks Bob. I changed that to just H6:h40, but unfortunately it's still
coming back with #NA.

 

[Dave Peterson]

Did you array enter the formula?

Are you sure that there's a match for all 3 criteria?

 

[hoosier41]

Thanks, yes I did enter it as an array and there is a match for all three
criteria.

 

[Dave Peterson]

I'd bet you were wrong.

Try finding the row with the 3 matches and reentering the values in those 3
cells. I bet that when you retype them, you'll fix the problem.