Need help with formula.. possibly an array?

x8xviperx6x · Mar 27, 2019

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!

DanteAmor · Mar 28, 2019

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:

Book1

A

B

C

D

E

F

L

M

N

AL

AM

AN

1

2

B

D

A

3

B

4

C

5

D

6

A1

7

B1

8

C1

Hoja3

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

Aladin Akyurek · Mar 28, 2019

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 · Mar 28, 2019

DanteAmor said:
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:

A B C D E F L M N AL AM AN
B D A
B
C C
A1
B1
C1

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FFFF00"]D[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #92D050"]D[/TD]
[TD="bgcolor: #92D050"]D[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>
Hoja3

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L2[/TH]
[TD="align: left"]=INDEX(AN1:AN7,SUMPRODUCT(((AM2:AM13=C2)+(AM2:AM13=D2)+(AM2:AM13=E2)+(AM2:AM13=F2))*(AN2:AN13<>"")*(ROW(AN2:AN13))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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 · Mar 28, 2019

Aladin Akyurek said:
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 · Mar 28, 2019

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 · Mar 28, 2019

DanteAmor said:
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!

Aladin Akyurek · Mar 28, 2019

Did you try post #3 at all?

DanteAmor · Mar 28, 2019

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

I'm glad to help you. I appreciate your kind comments.

Aladin Akyurek · Mar 28, 2019

x8xviperx6x said:
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...

Need help with formula.. possibly an array?

x8xviperx6x

New Member

Excel Facts

DanteAmor

Well-known Member

Aladin Akyurek

MrExcel MVP

x8xviperx6x

New Member

x8xviperx6x

New Member

DanteAmor

Well-known Member

x8xviperx6x

New Member

Aladin Akyurek

MrExcel MVP

DanteAmor

Well-known Member

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

Need help with formula.. possibly an array?

New Member

Excel Facts

Well-known Member

MrExcel MVP

New Member

New Member

Well-known Member

New Member

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock