I have a complicated array situation

[JoshuaFreeman]

I have a table where the text "pit1" appears in multiple different rows and columns (ex: "pit1" is in c7, j6, and p16). In the 3 columns next to each of these, there are time values. Ex: c7=pit1, d7= 03:15, e7=05:45, f7=8:15. I'm trying to write a formula that will search the entire spread sheet for "pit1" and then test if a time value appears in one of the 3 next columns and return some misc. value if true, and leave blank if false. Basically, something to the effect of: if "pit1" exist and has a value of time xx:xx then display misc info, else blank.

 

[pgc01]

Hi
Welcome to the board

I don't undertand what this means:

Basically, something to the effect of: if "pit1" exist and has a value of time xx:xx then display misc info, else blank.

What is misc info?

For the example you posted what would be the result?

 

[JoshuaFreeman]

The misc info would be a value of 1 or anything needed to satisfy conditional formatting.

 

[markmzz]

Maybe this:

Layout

		pit1	6:00	22:00	18:30					Result
pit2	7:00	12:00	18:00					pit1	6:00	found
	pit3	6:20	12:20	18:20				pit2	22:00
			pit4	6:02	12:01	23:00		pit3	6:20	found
								pit4	18:20
								*
								*
								*
*****	*****	*****	*****	*****	*****	*****	**	*****	*****	*****

<colgroup><col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 1170;" span="2"> <col width="34" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1243;" span="5"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 1170;"> <col width="34" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1243;"> <col width="36" style="width: 27pt; mso-width-source: userset; mso-width-alt: 1316;"> <tbody> </tbody>

Name (in green)

Data - Refers To: Sheet1!$A$1:$G$8


Formula

In K2 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IF(SUM(COUNTIF(OFFSET(Data,
IF(Data=$I2,ROW(Data)-ROW(INDEX(Data,1,1))),
IF(Data=$I2,COLUMN(Data)-COLUMN(INDEX(Data,1,1))+1),1,3),$J2)),"found","")

Markmzz

 

[JoshuaFreeman]

That might actually work. I'll let you know how it goes.

 

[JoshuaFreeman]

OMG, THANK YOU Markmzz!!!!!!!!!!!!! You're awesome. I'll have to study the formula to see how you did it. (I haven't fooled around with excel in 10 years and I stupidly told my boss that I was good with it). Thanks for saving my rear!

 

[markmzz]

OMG, THANK YOU Markmzz!!!!!!!!!!!!! You're awesome. I'll have to study the formula to see how you did it. (I haven't fooled around with excel in 10 years and I stupidly told my boss that I was good with it). Thanks for saving my rear!

You are welcome and thanks for the feedback.

Markmzz