Retrieving data with two variables

J

Jennifer

Hi,
First time. Looks like you all have some great advice. This will probably be
very easy for most of you.

I would like to create one worksheet were I choose a certain doctor (Drop
down box), the year and it matches the month, year and doctor and guves me
the correct production $$ for that doctor.
I have used the lookup formulas but can't seem to figure how I should write
this formula. Thank you for any help.
 
D

Dave Peterson

If you only give it the year, how will it match on the month?

If you give it a date, too, then you could use something like this:

I put my table of doctor's names, dates, and production amount on sheet2 in
A1:C10 (only 10 entries).

The in sheet1, I put the doctor's name in A1, the date in B1 and this formula in
C1:

=INDEX(Sheet2!C1:C10,MATCH(1,(Sheet2!A1:A10=A1)*
(TEXT(B1,"mmyyyy")=TEXT(Sheet2!B1:B10,"mmyyyy")),0))

(all one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

The formula expects real dates in sheet1's B1 and real dates in sheet2 B1:B10.
But it only uses the year and month.
 
Top