Function Help

[Scott@CW]

I am trying to write a function that will refernece to data valadation lists
then go and find the corresponding data. Below is an example of what I am
tring to get.

=IF(DATA!A2:A62000=India!C5,AND(IF(DATA!B2:B62000=India!C6,17,"No Data")))

IndiaC5 is a list of dates
IndiaC6 is a list of names

I want to find the first match of C5 and C6 then go over 17 rows and give me
that data. I cannot use a pivot table because this is a template for survey
results, and I am trying to pull just the contents from column R back into
this cell.

 

[Sandy Mann]

Scott,

Your explanation does not make sense, at least not to me.

If you want the data from Column R, (presumably in the Data sheet), where
the date in Column A of the Data sheet matches the date in cell C5 of the
India sheet and the date in Column B of the Data sheet matches the date in
cell C6 of the India sheet then try:

=IF(SUMPRODUCT((DATA!A2:A3001=India!C5)*(DATA!B2:B3001=India!C6)*ROW(2:3001))=0,"No
Data",INDEX(DATA!R2:R3001,SUMPRODUCT((DATA!A2:A3001=India!C5)*(India!B2:B3001=C6)*ROW(2:3001))))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Scott@CW]

I appreciate your help. I was able to make it work using a mutli vlookup.
To clarify cells C5 and C6 both contain a data valadation list of
information. The "master sheet" allowed a user to select a name and date from
those list and pull in survey data into the formated cells below. The problem
I was having had to do with the last part of the data which was a comment
section. All the comments are pasted into a cell in column R on the data
sheet on the first row of each persons name. Thank you for your formula, I am
going to try it as well.