finding correct values for appointed cells from other tables

Hi all, Im struggling to figure out how to do this, I have a list of people's local time zone & made a table in a timeline style where I put 1's & 0's as value & using that to graph a chart, att he same time Im also trying to make another table next to it that is the conversion time zone of mines (GMT-8), where I already had formulas in each cell telling me what time the appointed cell would be. However, Im trying to convert that conversion table into same tyle as my 1st table where I use 1's & 0's, so that I can graph a chart of all people compare into my time zone.

At first I tried using HLOOKUP, but it didnt work. it wont see the value. Im hoping if any of u maybe have a better idea than mines or able to correct my HLOOKUP usage method. attached is the worksheet Im working on. The final product I want is the 3rd table on the right where it say "Based On GMT-8 time line. Right now those empty cells & some are manually put in, what I want is to have a formula or some sort method that can auto lookup values comparing the conversion table & local timeline table & put the correct 1's & 0's.

Any help would be appreciated.

OMG yes!!! that's it, u got it! THANKS SO MUCH!

I see what u did on conversion table, its much more cleaner than what I did, but may I ask what's the function =IF(ISNUMBER(AG10),1,0) how that works? I never use that function before.

Again, thankyou sooooo much!!

oh wait... my mistake, result not yet correct, i forgot the most important part, which is the right side time has to match the conversion table... hold on let me make screenshot explain

Originally Posted by FDibbins

Just in case Glenn does not get back to you, what that does is 1st test to see if AG10 contains a number/value (as opposed to text). If it does, it returns a 1, else it returns a 0

You also have used just...
=--ISNUMBER(AG10)

ahh... good to know, appreciated the explanation.

Now only the last part, which is the most important part needs to solve.

With Glenn's ISNUMBER() function, we'll able to get this..
Attachment 498943

But what I want is to have the hours pointed to the correct "Headers" of that table. Which the final result should look like this.
Attachment 498944

Now Glenn's improvement gave me a new idea, is there a function I can put in the ROW that will search the same HOURS that matches the HEADERS & put a 1's value in cell?

yes, please, will wait for ur reply.

u mean the screenshots? or u mean the excel file?

just in case I upload both.

Ok, now I had new development. I used COUNTIF function, it did solved the problem where I need the 3rd table (Blue) to show 1's & 0's that matches the conversion table. but it came with new problem. The Date/Time conversion value is not the same as the headers. Now the problem is at the 2nd table (Conversion table).

What i change:
BD9=COUNTIF($AF9:$BC9,BD$6)

I attached my worksheet with new changes.

Now I need to find out how to convert those time zone differences but remain within the SAME DAY.