| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup
A B C D E F G
Project Jan Feb Mar Apr May Total A1 2 3 4 5 6 20 A2 2 4 6 8 20 A3 7 9 16 A4 10 10 Summary A B C Total Savings Starting Month A1 20 Jan A2 20 Feb A3 16 Apr A4 10 May Colm B to F show month data, while data under COLM A show project names When I create the summary report,I want a formula that will show the First month under Colmn C, when savings start, I am dealing with rougly 2000 projects spread over in 120 Locations. Any help please |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup
Assuming that on Sheet1, A1:G5 contains the data, and that on Sheet2,
A2:A5 contains A1, A2, A3, and A4, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER... C2, copied down: =INDEX(Sheet1!$B$1:$F$1,MATCH(TRUE,INDEX(Sheet1!$B $2:$F$5,MATCH(A2,Sheet1 !$A$2:$A$5,0),0)<"",0)) Adjust the ranges accordingly. Hope this helps! In article , Ananth wrote: A B C D E F G Project Jan Feb Mar Apr May Total A1 2 3 4 5 6 20 A2 2 4 6 8 20 A3 7 9 16 A4 10 10 Summary A B C Total Savings Starting Month A1 20 Jan A2 20 Feb A3 16 Apr A4 10 May Colm B to F show month data, while data under COLM A show project names When I create the summary report,I want a formula that will show the First month under Colmn C, when savings start, I am dealing with rougly 2000 projects spread over in 120 Locations. Any help please |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup
Worked Immaculately . Thanks very much
"Domenic" wrote: Assuming that on Sheet1, A1:G5 contains the data, and that on Sheet2, A2:A5 contains A1, A2, A3, and A4, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER... C2, copied down: =INDEX(Sheet1!$B$1:$F$1,MATCH(TRUE,INDEX(Sheet1!$B $2:$F$5,MATCH(A2,Sheet1 !$A$2:$A$5,0),0)<"",0)) Adjust the ranges accordingly. Hope this helps! In article , Ananth wrote: A B C D E F G Project Jan Feb Mar Apr May Total A1 2 3 4 5 6 20 A2 2 4 6 8 20 A3 7 9 16 A4 10 10 Summary A B C Total Savings Starting Month A1 20 Jan A2 20 Feb A3 16 Apr A4 10 May Colm B to F show month data, while data under COLM A show project names When I create the summary report,I want a formula that will show the First month under Colmn C, when savings start, I am dealing with rougly 2000 projects spread over in 120 Locations. Any help please |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup
I am too impressed by this solution. It is producing fanatastic results.
However, can you how this is working "Domenic" wrote: Assuming that on Sheet1, A1:G5 contains the data, and that on Sheet2, A2:A5 contains A1, A2, A3, and A4, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER... C2, copied down: =INDEX(Sheet1!$B$1:$F$1,MATCH(TRUE,INDEX(Sheet1!$B $2:$F$5,MATCH(A2,Sheet1 !$A$2:$A$5,0),0)<"",0)) Adjust the ranges accordingly. Hope this helps! In article , Ananth wrote: A B C D E F G Project Jan Feb Mar Apr May Total A1 2 3 4 5 6 20 A2 2 4 6 8 20 A3 7 9 16 A4 10 10 Summary A B C Total Savings Starting Month A1 20 Jan A2 20 Feb A3 16 Apr A4 10 May Colm B to F show month data, while data under COLM A show project names When I create the summary report,I want a formula that will show the First month under Colmn C, when savings start, I am dealing with rougly 2000 projects spread over in 120 Locations. Any help please |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup
Based on the sample data provided, if we take a look at the formula in
C4, on Sheet2, here' s how it's evaluated... =INDEX(Sheet1!$B$1:$F$1,MATCH(TRUE,INDEX(Sheet1!$B $2:$F$5,MATCH(A4,Sheet1 !$A$2:$A$5,0),0)<"",0)) ===== =INDEX({"Jan","Feb","Mar","Apr","May"},MATCH(TRUE, INDEX({2,3,4,5,6;"",2,4 ,6,8;"","","",7,9;"","","","",10},MATCH("A3",{"A1" ;"A2";"A3";"A4"},0),0)< "",0)) ===== =INDEX({"Jan","Feb","Mar","Apr","May"},MATCH(TRUE, INDEX({2,3,4,5,6;"",2,4 ,6,8;"","","",7,9;"","","","",10},3,0)<"",0)) ===== =INDEX({"Jan","Feb","Mar","Apr","May"},MATCH(TRUE, {"","","",7,9}<"",0)) ===== =INDEX({"Jan","Feb","Mar","Apr","May"},MATCH(TRUE, {FALSE,FALSE,FALSE,TRUE ,TRUE},0)) ===== =INDEX({"Jan","Feb","Mar","Apr","May"},4) ....which returns Apr. Hope this helps! In article , Ananth wrote: I am too impressed by this solution. It is producing fanatastic results. However, can you how this is working "Domenic" wrote: Assuming that on Sheet1, A1:G5 contains the data, and that on Sheet2, A2:A5 contains A1, A2, A3, and A4, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER... C2, copied down: =INDEX(Sheet1!$B$1:$F$1,MATCH(TRUE,INDEX(Sheet1!$B $2:$F$5,MATCH(A2,Sheet1 !$A$2:$A$5,0),0)<"",0)) Adjust the ranges accordingly. Hope this helps! In article , Ananth wrote: A B C D E F G Project Jan Feb Mar Apr May Total A1 2 3 4 5 6 20 A2 2 4 6 8 20 A3 7 9 16 A4 10 10 Summary A B C Total Savings Starting Month A1 20 Jan A2 20 Feb A3 16 Apr A4 10 May Colm B to F show month data, while data under COLM A show project names When I create the summary report,I want a formula that will show the First month under Colmn C, when savings start, I am dealing with rougly 2000 projects spread over in 120 Locations. Any help please |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
| Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
| Advanced Lookup (lookup for 2 values) | Excel Worksheet Functions | |||
| Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
| How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) | |||
Linear Mode
