Formula to return a blank or an X based on looking up a value in an array

[husker77c]

I am building a sheet to track linear progress for a construction company, think road work.

We use survey stationing as our measurement system. 0000+00 Equals 0', 0001+00 equals 100' and so on. The progress line pic below is a visual interpretation of our work area.

I need to build a formula to look up a value in an array. If the value is in the array i need to return an X in a cell.

The array is how the data is presented to me, i enter it in to the array. I need a formula that will place an X in the cell that corresponds to the same numbers in the progress line to visually show linear progress. In the array there will occasionally be skips in linear progress. so i need to show a skip in locations that are not complete.

Its kind of confusing to show what I need, but in theory I don't think this should be that hard. I know it is possible to build because I've seen sheets my peers have made but I cant seem to figure it out.

 

[Domenic]

Unclear, can you please elaborate?

Also, can you post the actual results that you expect for your progress line?

Also, when posting any data, please post the data as text so that we can easily copy and paste it into Excel so that we can use it to come up with a solution. Alternatively, you can use the add-in xl2BB to post your data.

 

[husker77c]

Below is what I would like my progress line to look like.

	0001+00	0002+00	0003+00	0004+00	0005+00	0006+00	0007+00	0008+00
Crew 1	x	x	x		x	x	x	x
Crew 2		x	x	x	x		x	x

Crew 1
Day Start Day finish

0001+00	0002+00
0002+00	0003+00
0004+00	0005+00
0005+00	0006+00
0006+00	0007+00
0007+00	0008+00

Crew 2
Day Start Day Finish

0002+00	0003+00
0003+00	0004+00
0004+00	0005+00
0005+00	0006+00
0007+00	0008+00

In the above tables the top is how I would like my progress line to look.


The data in the second 2 tables represent the information provided to me by crews 1 and 2. Crew 1 completed all the areas except for 0003+00 to 0004+00, you can tell an area was skipped when the starting point of the day does not equal the previous days ending point. I need an X in the progress line of all the areas that are completed but not in the areas not completed.

Crew 2 did not complete from 0001+00 to 0002+00 and also did not complete from 0006+00 to 0007+00.

I believe I need to look in the Crew tables for values that are equal to the numbers on the progress line but not equal to others and return an X if it falls between the progress numbers and no X if it does not. This is very hard to explain, I hope this helps.

 

[Domenic]

Sorry, but I don't understand your logic.

With regards to Crew 1, you say that 0004+00 is blank because the 0003+00 to 0004+00 slot in the table doesn't exist. So why does 0001+00 have an x when the 0000+00 to 0001+00 slot doesn't exist?

Same thing with regards to Crew 2, why does 0007+00 have an x when the 0006+00 to 0007+00 slot in the table doesn't exist? And then why is 0006+00 is blank when the 0005+00 to 0006+00 slot exists? And so on.