Function Help
 

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.

Function Help
 

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:B 3001=India!C6)*ROW(2:3001))=0,"No
Data",INDEX(DATA!R2:R3001,SUMPRODUCT((DATA!A2:A300 1=India!C5)*(India!B2:B3001=C6)*ROW(2:3001))))

--
HTH

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


with @tiscali.co.uk


"Scott@CW" wrote in message
...
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.

Function Help
 

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.

"Sandy Mann" wrote:

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:B 3001=India!C6)*ROW(2:3001))=0,"No
Data",INDEX(DATA!R2:R3001,SUMPRODUCT((DATA!A2:A300 1=India!C5)*(India!B2:B3001=C6)*ROW(2:3001))))

--
HTH

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


with @tiscali.co.uk


"Scott@CW" wrote in message
...
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.