#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 51
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 150
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 51
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 51
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 150
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup JBush Excel Worksheet Functions 3 January 4th 07 12:14 AM
Sumproduct - Condition based on lookup of a Lookup Hari Excel Discussion (Misc queries) 12 May 31st 06 09:28 AM
Advanced Lookup (lookup for 2 values) 0-0 Wai Wai ^-^ Excel Worksheet Functions 2 March 30th 06 07:09 PM
Pivot table doing a lookup without using the lookup function? NGASGELI Excel Discussion (Misc queries) 0 August 2nd 05 05:08 AM
How do I lookup and return different values when the lookup value. kg Excel Discussion (Misc queries) 1 January 20th 05 01:53 AM


All times are GMT +1. The time now is 10:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"