Index/ Match with Different size arrays

[schang_825]

Hi all,

This seems like it would be a rather simple problem, but for some reason, I can't seem to figure this out. I am currently working on a new high rise condiminium project, with approximately 488 units that need to be inspected. I created a schedule, with the dates across Row 7 and the times (ex: 8am, 9 am, 10 am, etc.) down Column C. The unit number will be entered into the cell that correlates to the time and date that unit will be inspected.

In another sheet, I have a table of all the unit numbers that details other information. In one column, I want to be able to enter the date it will be inspected. What I want to happen is to have this date populate automatically from the Schedule sheet. I tried using an Index/Match formula, but because the array sizes are different, it doesn't seem to be working.

This is the formula I used:

=INDEX(Schedule!$D7:$CJ7, MATCH(G209,Schedule!$D9:$CJ23,0))

Can anyone help me out with this??

Thanks in advance!

 

[Haseeb Avarakkan]

Hello,

Try this Array Formula. Confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

=INDEX(Schedule!$A7:$CJ7,SMALL(IF(Schedule!$D9:$CJ23=G209,COLUMN(Schedule!$D9:$CJ23)),1))

 

[schang_825]

Thanks for your reply, and sorry for the late response. I wasn't working over the weekend...Anyways, I tried that formula, and it doesn't work...It gives me the date two columns to the right.

 

[schang_825]

Oops, never mind! I had put in D7:CJ7 instead of A7:CJ7. Thanks, it works great! But is there a way we can put in blanks if the unit has not been scheduled yet? Currently, I get a #NUM! error.

 

[T. Valko]

Oops, never mind! I had put in D7:CJ7 instead of A7:CJ7. Thanks, it works great! But is there a way we can put in blanks if the unit has not been scheduled yet? Currently, I get a #NUM! error.

Try it like this...

Still array entered.

=IF(COUNTIF(Schedule!$D9:$CJ23,G209),INDEX(Schedule!$A7:$CJ7,MIN(IF(Schedule!$D9:$CJ23=G209,COLUMN(Schedule!$D9:$CJ23)))))

 

[schang_825]

Thanks, this works great!

Another problem though. If I also wanted to put in the time the unit is inspected (which is listed in Row B) into another cell, what formula would I use? I assume it would be similar to the one we just used, and I played around with it, but can't seem to get it to work...

 

[T. Valko]

Hi all,

This seems like it would be a rather simple problem, but for some reason, I can't seem to figure this out. I am currently working on a new high rise condiminium project, with approximately 488 units that need to be inspected. I created a schedule, with the dates across Row 7 and the times (ex: 8am, 9 am, 10 am, etc.) down Column C. The unit number will be entered into the cell that correlates to the time and date that unit will be inspected.

In another sheet, I have a table of all the unit numbers that details other information. In one column, I want to be able to enter the date it will be inspected. What I want to happen is to have this date populate automatically from the Schedule sheet. I tried using an Index/Match formula, but because the array sizes are different, it doesn't seem to be working.

This is the formula I used:

=INDEX(Schedule!$D7:$CJ7, MATCH(G209,Schedule!$D9:$CJ23,0))

Can anyone help me out with this??

Thanks in advance!

Thanks, this works great!

Another problem though. If I also wanted to put in the time the unit is inspected (which is listed in Row B) into another cell, what formula would I use? I assume it would be similar to the one we just used, and I played around with it, but can't seem to get it to work...

I thought the times were in column C?

 

[schang_825]

Sorry, I changed the time column to Column B and Column C now has the Inspector #. There will be 3 different inspectors assigned to different time slots.

But I figured the formula out. I used:

=IF(COUNTIF(Schedule!$D$10:$CJ$24,D209),INDEX(Schedule!$B$1:$B$24,MIN(IF(Schedule!$D$10:$CJ$24=D209,ROW(Schedule!$D$10:$CJ$24)))))

Thanks!

 

[T. Valko]

Sorry, I changed the time column to Column B and Column C now has the Inspector #. There will be 3 different inspectors assigned to different time slots.

But I figured the formula out. I used:

=IF(COUNTIF(Schedule!$D$10:$CJ$24,D209),INDEX(Schedule!$B$1:$B$24,MIN(IF(Schedule!$D$10:$CJ$24=D209,ROW(Schedule!$D$10:$CJ$24)))))

Thanks!

Good deal. Thanks for the feedback!