# Need help with formula.. possibly an array?

#### x8xviperx6x

##### New Member
First off sorry for not being able to insert as an excel table I couldn't figure out how to input such a large table..

I’m having issues coming up with a formula that will get theresults I need. Below is an image of the data on my sheet.

What is displayed in column A is the days of the year.Column C is the shift codes that each shift works. If blank they are off. Inthe array AM2:BK13 displays what each shift hours are in accordance to theshift code.

Columns C:F can change daily, its controlled by datavalidation using a drop down menu changed by user input.

The problems is L:AI columns for the entire year needs to autopopulate as the user changes the schedule. Thus resulting in an easy copy pasteschedule.

In cell L2 I’ve tried several IF formulas with a vlookupand/or Index/matching and the problem is vlookup and index matching result in a0 when there is a blank in the array am2:bk13 thus ending the formulaprematurely.

L2 example formula:

=IF(C2="A",VLOOKUP(C2,AM2:BK13,2,FALSE),IF(D2="C",VLOOKUP(D2,AM2:BK13,2,FALSE),IF(E2="B",VLOOKUP(E2,AM2:BK13,2,FALSE),IF(F2="D",VLOOKUP(F2,AM2:BK13,2,FALSE)))))

But my formula results in a 0 when it gets to the if(e2=”B”…results in true returns a 0, and doesn’t continue to the last lookup. Which I don’tunderstand why it’s a zero instead of blank for that hr range, and continue onto the last IF statement.

I thought about adding the AND statement for the AND(e2=”B”,am2<>””)but that is a lot more input to the formula. Example: The letters in RED arethe result I need for the auto population.

I'm going to guess I need to start researching array formulas...
Any help would be greatly appreciated, thanks in advanced!

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### DanteAmor

##### Well-known Member
In theory, if the result of looking for E2 is white, then you would have to put another IF, so that you can continue with the F2 search, for example:

=IF(C2="A",VLOOKUP(C2,AM2:BK13,2,FALSE),IF(D2="C",VLOOKUP(D2,AM2:BK13,2,FALSE),
IF(E2="B",
IF(VLOOKUP(E2,AM2:BK13,2,FALSE)
<>"",
VLOOKUP(E2,AM2:BK13,2,FALSE),
IF(F2="D",
VLOOKUP(F2,AM2:BK13,2,FALSE)))))

You should also do it for C2 and D2

Try the following:

##### MrExcel MVP
Just considering the formula with VLOOKUP you posted...

=IF(C2="A",VLOOKUP(C2,AM2:BK13,2,FALSE),IF(D2="C",VLOOKUP(D2,AM2:BK13,2,FALSE),IF(E2="B",VLOOKUP(E2,AM2:BK13,2,FALSE),IF(F2="D",VLOOKUP(F2,AM2:BK13,2,FALSE)))))

can be rewritten as

=VLOOKUP(LOOKUP(REPT("z",255),C2:F2),
AM2:BK13,2,0)

assuming that C2:F2 contains just one value at any time.
<strike>
</strike>

#### x8xviperx6x

##### New Member
In theory, if the result of looking for E2 is white, then you would have to put another IF, so that you can continue with the F2 search, for example:

=IF(C2="A",VLOOKUP(C2,AM2:BK13,2,FALSE),IF(D2="C",VLOOKUP(D2,AM2:BK13,2,FALSE),
IF(E2="B",
IF(VLOOKUP(E2,AM2:BK13,2,FALSE)
<>"",
VLOOKUP(E2,AM2:BK13,2,FALSE),
IF(F2="D",
VLOOKUP(F2,AM2:BK13,2,FALSE)))))

You should also do it for C2 and D2

Try the following:

ABCDEFLMNALAMAN
1
2BDDA
3B
4CC
5DD
6A1
7B1
8C1

</tbody>
Hoja3

Worksheet Formulas
CellFormula
L2=INDEX(AN1:AN7,SUMPRODUCT(((AM2:AM13=C2)+(AM2:AM13=D2)+(AM2:AM13=E2)+(AM2:AM13=F2))*(AN2:AN13<>"")*(ROW(AN2:AN13))))

</tbody>

<tbody>
</tbody>

Yes, that is correct, for it to continue the search i'd need to add IF statements to have it continue each column.
I tested out your index formula and it works up until the 4th row it gives me #REF errors, that's when all four shifts work a 6hr portion of the day and it is identified as shift A1 is first half, etc. Although A1 works B thru D show #REF .

#### x8xviperx6x

##### New Member

Just considering the formula with VLOOKUP you posted...

=IF(C2="A",VLOOKUP(C2,AM2:BK13,2,FALSE),IF(D2="C",VLOOKUP(D2,AM2:BK13,2,FALSE),IF(E2="B",VLOOKUP(E2,AM2:BK13,2,FALSE),IF(F2="D",VLOOKUP(F2,AM2:BK13,2,FALSE)))))

can be rewritten as

=VLOOKUP(LOOKUP(REPT("z",255),C2:F2),
AM2:BK13,2,0)

assuming that C2:F2 contains just one value at any time.
<strike>
</strike>

I will have to test that out, that is significantly shorter, unfortunately at all times column C2:F2 will have 2 or more letters

#### DanteAmor

##### Well-known Member
Sorry, In the first formula I omitted change 7 by 13

You have to check the relative and absolute part of the cells.

=INDEX(\$AN\$1:\$AN\$13,SUMPRODUCT(((\$AM\$2:\$AM\$13=C2)+(\$AM\$2:\$AM\$13=D2)+(\$AM\$2:\$AM\$13=E2)+(\$AM\$2:\$AM\$13=F2))*(\$AN\$2:\$AN\$13<>"")*(ROW(\$AN\$2:\$AN\$13))))

Try again

#### x8xviperx6x

##### New Member

Sorry, In the first formula I omitted change 7 by 13

You have to check the relative and absolute part of the cells.

=INDEX(\$AN\$1:\$AN\$13,SUMPRODUCT(((\$AM\$2:\$AM\$13=C2)+(\$AM\$2:\$AM\$13=D2)+(\$AM\$2:\$AM\$13=E2)+(\$AM\$2:\$AM\$13=F2))*(\$AN\$2:\$AN\$13<>"")*(ROW(\$AN\$2:\$AN\$13))))

Try again

Flawless, I overlooked that too! Brilliant formula, thank you very much!

##### MrExcel MVP
Did you try post #3 at all?

#### DanteAmor

##### Well-known Member
Flawless, I overlooked that too! Brilliant formula, thank you very much!

##### MrExcel MVP
I will have to test that out, that is significantly shorter, unfortunately at all times column C2:F2 will have 2 or more letters

Are they prioritized? If not, are you intending to sum the results? If the latter, the results must be numeric...

Replies
3
Views
85
Replies
6
Views
69
Replies
7
Views
123
Replies
1
Views
207
Replies
8
Views
349