Solved: Return based on mutliple criteria

[Pizzafiend]

Greetings,
I have 2 list boxes (Various Names and Date) and I want to extract a value on another page based on those 2 choices. I am thinking INDEX and MATCH but I am not sure how to lay it out. Any help would be appreciated! Thanks!

[MikeWolfeJr]

Hi...

Can you give a little more detail? Are the listboxes on a form or on a worksheet? How is the data laid-out on the other worksheet from which you want to do the lookup? Where is the the extracted value to be placed?

You may be able to get away with using VLookup.

[Pizzafiend]

Hello,

The list boxes are currently cells using data validation. One for misc. names and one for misc dates. When the user chooses a name and date I need to fill in various catagories in a table. ie. hours worked, or the individuals function for that month.
Select Employee: (listbox)
Select Month: (listbox)
Hours Worked: Result

[lucas]

Good questions Mike. Pizzafiend, would it be possible for you to post your file here so we can take a look at it....be sure to strip out any personal information so that its a generic example. Just zip it up and use manage attachments at the bottom of the post reply page.

[Bob Phillips]

Hello,

The list boxes are currently cells using data validation. One for misc. names and one for misc dates. When the user chooses a name and date I need to fill in various catagories in a table. ie. hours worked, or the individuals function for that month.
Select Employee: (listbox)
Select Month: (listbox)
Hours Worked: Result

A few assumptions

The hours worked is a table say B2:N16, where months are C2:N2, employees are B3:B16. The result comes from

=OFFSET(B2,MATCH(Employee,B3:B16,0),MATCH(Month,C2:N2,0))

[Pizzafiend]

Here is a sample sheet that I threw together. It represents the problem on a minor scale (thought it would be easier to work with instead of the actual project I am working on). Again, any help or direction would be greatly appreciated. Thanks!

Danny

[Shazam]

Use this formula in cell C2:


=LOOKUP(2,1/((Sheet2!A2:A100=A2)*(Sheet2!B2:B100=B2)),Sheet2!C2:C100)

[Bob Phillips]

Here is a sample sheet that I threw together. It represents the problem on a minor scale (thought it would be easier to work with instead of the actual project I am working on). Again, any help or direction would be greatly appreciated. Thanks!

Danny

=INDEX(Sheet2!C2:C36,MATCH(A2&B2,Sheet2!A2:A36&Sheet2!B2:B36,0))

as an array formula

[Pizzafiend]

Thanks xld and Shazam, both of those seem to work great.

xld, I had the formula that you had, but I didn't match the array after the index statement with the other columns I was referencing. At least I can rest easy that I wasn't totally off base.