Hello,

Sheet 1 is the Data and Sheet 2 is the result I need.

Sheet 1: (DATA)
A B
1 GROUP 1 Hours:Minutes
2 **HOLIDAY 421:35
3 ~SHIFT 4560:25
4 OT 851:43
5 *UNDERTIME 48:52
6 GROUP 2 Hours:Minutes
7 SHIFT 3821:56
8 OT 450:21
9 *VACATION 68:25

SHEET 2 (Result)

A B C
1 SEGMENTS GROUP 1 GROUP 2
2 SHIFT 4560:25 3821:56
3 OT 851:43 450:21
4 HOLIDAY 421:25 0
5 VACATION 0 68:25
6 UNDERTIME 48:52 0

How can I use VLOOKUP to get the result I need? I actually have 20 Groups
and have different segments in each group. Is there other formula I can use?

Thanks in advance!

I would use a helper column. Insert a column in Sheet1 at column A. Now
your segment information and hours are in columns B and C. Then put this
formula in A2 and copy down

=INDEX(B$1:B2,MAX(ISNUMBER(SEARCH("Group",B$1:B2)) *ROW(INDIRECT("1:"&ROWS(B$1:B2)))))&" "&B2

It's an array formula, so hit Ctrl+Shift+Enter after typing it in or
copy/pasting it in.

Then enter this in Sheet2!B2

=IF(ISNA(MATCH(B$1&" "&$A2,Sheet1!$A$2:$A$9,0)),0,VLOOKUP(B$1&"
"&$A2,Sheet1!$A$2:$C$9,3,0))


"al_ba" wrote:

Hello,

Sheet 1 is the Data and Sheet 2 is the result I need.

Sheet 1: (DATA)
A B
1 GROUP 1 Hours:Minutes
2 **HOLIDAY 421:35
3 ~SHIFT 4560:25
4 OT 851:43
5 *UNDERTIME 48:52
6 GROUP 2 Hours:Minutes
7 SHIFT 3821:56
8 OT 450:21
9 *VACATION 68:25

SHEET 2 (Result)

A B C
1 SEGMENTS GROUP 1 GROUP 2
2 SHIFT 4560:25 3821:56
3 OT 851:43 450:21
4 HOLIDAY 421:25 0
5 VACATION 0 68:25
6 UNDERTIME 48:52 0

How can I use VLOOKUP to get the result I need? I actually have 20 Groups
and have different segments in each group. Is there other formula I can use?

Thanks in advance!

Hi JMB,

This one worked! I just have to make some tweakings to match my data.
The formula is new to me but I am learning. :)
Thank you for your time and help!! :)

"JMB" wrote:

I would use a helper column. Insert a column in Sheet1 at column A. Now
your segment information and hours are in columns B and C. Then put this
formula in A2 and copy down

=INDEX(B$1:B2,MAX(ISNUMBER(SEARCH("Group",B$1:B2)) *ROW(INDIRECT("1:"&ROWS(B$1:B2)))))&" "&B2

It's an array formula, so hit Ctrl+Shift+Enter after typing it in or
copy/pasting it in.

Then enter this in Sheet2!B2

=IF(ISNA(MATCH(B$1&" "&$A2,Sheet1!$A$2:$A$9,0)),0,VLOOKUP(B$1&"
"&$A2,Sheet1!$A$2:$C$9,3,0))


"al_ba" wrote:

Hello,

Sheet 1 is the Data and Sheet 2 is the result I need.

Sheet 1: (DATA)
A B
1 GROUP 1 Hours:Minutes
2 **HOLIDAY 421:35
3 ~SHIFT 4560:25
4 OT 851:43
5 *UNDERTIME 48:52
6 GROUP 2 Hours:Minutes
7 SHIFT 3821:56
8 OT 450:21
9 *VACATION 68:25

SHEET 2 (Result)

A B C
1 SEGMENTS GROUP 1 GROUP 2
2 SHIFT 4560:25 3821:56
3 OT 851:43 450:21
4 HOLIDAY 421:25 0
5 VACATION 0 68:25
6 UNDERTIME 48:52 0

How can I use VLOOKUP to get the result I need? I actually have 20 Groups
and have different segments in each group. Is there other formula I can use?

Thanks in advance!

glad to hear that worked.

"al_ba" wrote:

Hi JMB,

This one worked! I just have to make some tweakings to match my data.
The formula is new to me but I am learning. :)
Thank you for your time and help!! :)

"JMB" wrote:

I would use a helper column. Insert a column in Sheet1 at column A. Now
your segment information and hours are in columns B and C. Then put this
formula in A2 and copy down

=INDEX(B$1:B2,MAX(ISNUMBER(SEARCH("Group",B$1:B2)) *ROW(INDIRECT("1:"&ROWS(B$1:B2)))))&" "&B2

It's an array formula, so hit Ctrl+Shift+Enter after typing it in or
copy/pasting it in.

Then enter this in Sheet2!B2

=IF(ISNA(MATCH(B$1&" "&$A2,Sheet1!$A$2:$A$9,0)),0,VLOOKUP(B$1&"
"&$A2,Sheet1!$A$2:$C$9,3,0))


"al_ba" wrote:

Hello,

Sheet 1 is the Data and Sheet 2 is the result I need.

Sheet 1: (DATA)
A B
1 GROUP 1 Hours:Minutes
2 **HOLIDAY 421:35
3 ~SHIFT 4560:25
4 OT 851:43
5 *UNDERTIME 48:52
6 GROUP 2 Hours:Minutes
7 SHIFT 3821:56
8 OT 450:21
9 *VACATION 68:25

SHEET 2 (Result)

A B C
1 SEGMENTS GROUP 1 GROUP 2
2 SHIFT 4560:25 3821:56
3 OT 851:43 450:21
4 HOLIDAY 421:25 0
5 VACATION 0 68:25
6 UNDERTIME 48:52 0

How can I use VLOOKUP to get the result I need? I actually have 20 Groups
and have different segments in each group. Is there other formula I can use?

Thanks in advance!