Excel lookup from different Columns (Ex 2003)

[stagnut]

Hi Guys,
Hopefully someone can assist.
I have a weekly spreadsheet (on left) that is currently filled in by hand on a daily basis. No problems except time consumimg. The downloaded report is a daily one where the info comes from(on Right) can sometimes include all Report Groups, or as shown, not always.
Normally get by as a novice, by checking how others do the different lookups, but I am stumped by this one. I have tried different Vlookups and some Index Match that I know, but because the downloaded report doesn't always include all the Report Groups, I keep getting the wrong answers.
Many thanks for looking,

Excel 2003
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3	Weekly Spreadsheet		Monday		Tuesday		ETC.		Daily System Report
4		Report Group	Sku	Hours	Perf	Hours				Report Group	Sku	Hours
5	A1								A1
6		ONE	82	28						ONE	82	28
7		TWO								FOUR	58	3
8		THREE							Subtotal:	A1	80	31
9		FOUR	58	3					A2
10	Subtotal:	A1	80	31						ONE	66	50
11	A2									TWO	125	1
12		ONE	66	50						FOUR	49	9
13		TWO	125	1					Subtotal:	A2	65	60
14		THREE							A3
15		FOUR	49	9						ONE	78	66
16	Subtotal:	A2	65	60						FOUR	61	0
17	A3								Subtotal:	A3	78	66
18		ONE							A4
19		TWO								ONE	78	119
20		THREE								THREE	69	4
21		FOUR								FOUR	51	8
22	Subtotal:	A3							Subtotal:	A4	76	131
23	A4								A5
24		ONE								TWO	68	38
25		TWO								THREE	181	0
26		THREE								FOUR	78	2
27		FOUR							Subtotal:	A5	68	40
28	Subtotal:	A4							A6
29	A5									TWO	83	43
30		ONE								FOUR	49	3
31		TWO							Subtotal:	A6	81	45
32		THREE
33		FOUR
34	Subtotal:	A5
35	A6
36		ONE
37		TWO
38		THREE
39		FOUR
40	Subtotal:	A6
Sheet1

 

[fibonacci1101]

Check if this formula works for you

https://app.box.com/s/npnhr3ey4n0xfvo5wkps

 

[stagnut]

Looking good from what I can see at home. Will check at work tomorrow. Thanks,

 

[fibonacci1101]

If you are using Excel 2003 you will have to modify that formula a little bit cause Excel 2003 doesn't support IFERROR function. Try this:

=IF(ISERROR(VLOOKUP($B4,INDIRECT("J"&MATCH($A$3,$I:$I,0)&":L"&MATCH($A$3,$J:$J,0)),COLUMN(C4)-1,0)),"",VLOOKUP($B4,INDIRECT("J"&MATCH($A$3,$I:$I,0)&":L"&MATCH($A$3,$J:$J,0)),COLUMN(C4)-1,0))

I bolded the additional parts of formula

 

[stagnut]

Hi fibonacci1101
Many thanks, it all works ok using Excel 2003. However I have problem splitting the two into different worksheets.
The report on the Right, is system generated and is saved on another worksheet from column A. I have changed the formulas to take that into acound, but the "J" and the ":L" do not like being changed to "B" and ":D to feed from the other worksheet. I have cut from "I" and then pasted into another Worksheet from "A"but although the rest of the formula picks up the change the the "J" and the ":L" stay the same. Any ideas would be very much appreciated. Thanks again.

 

[fibonacci1101]

Check this out:

https://app.box.com/s/npnhr3ey4n0xfvo5wkps

 

[stagnut]

Hi, thanks for the update and apologies for not replying sooner. You have excactly the same as me, but the formulas still contain "J" & ":L", which do not pick up from the daily report. Also, the updated formula includes "sheet2" which is now "Daily System Report. I still get nothing when I change them to include "Daily System Report". Thanks for your help so far.

 

[stagnut]

Hi I have managed to change the formula's so that they now appear to work ok.
C4 in the https://app.box.com/s/npnhr3ey4n0xfvo5wkps I now have

=IF(ISERROR(VLOOKUP($B4,INDIRECT("'Daily System Report'!B"&MATCH($A$3,'Daily System Report'!$A:$A,0)&":D"&MATCH($A$3,'Daily System Report'!$B:$B,0)),COLUMN(C4)-1,0)),"",VLOOKUP($B4,INDIRECT("'Daily System Report'!B"&MATCH($A$3,'Daily System Report'!$A:$A,0)&":D"&MATCH($A$3,'Daily System Report'!$B:$B,0)),COLUMN(C4)-1,0))