Please Please Help

[Guest]

Hi, I am trying to create a formula that will look up information from
columns that are not next to each.

Example
A B C D E F G G
1 Jan Jan Feb Feb Mar Mar Apr Apr
2 Sub Adj Sub Adj Sub Adj Sub Adj
3
4 10 20 10 20 10 20 10 20
5 20 10 20 10 20 10 20 10

I have a file with the full year data and I need to read this data into
another spreadsheet. If I put in Jan on my other spreadsheet I want the
information from the Jan columns to be brought over. If I use HLOOKUP it
only brings the Sub information. Hope I explained it ok, please help.

 

[Guest]

Is the data for Jan in 2 columns in your new sheet? and no limit on rows? And
you say columns which are NOT next to each other ... what exacly do you mean
.... data for Jan then data for Mar??

What is current formula?

 

[Guest]

Thanks for the quick repsonse

I have a report that has Sub and Adj column headings in it. I have to do a
report each month. What I am trying to do is when I enter the current month
date in the report that it looks up the data from the second sheet and brings
it over under the correct heading (sub and adj).

So based on the example if I entered Mar on the report I would get the 20
under sub and 10 under adj for the first line then 10 under sub and 20 under
adj on the second line and so on.

Hope this explains a better.

 

[Guest]

Hi,
Assuming the output data fomat is the same as the input then:

on OUTPUT sheet: Month in A1 and B1, "Sub" & "Adj" in A2 & B2 then in:

A4:
=IF(OFFSET(Sheet1!$A$1,ROW()-1,MATCH(A$1,Sheet1!$1:$1,0)-1)<>"",OFFSET(Sheet1!$A$1,ROW()-1,MATCH(A$1,Sheet1!$1:$1,0)-1),"")

in B4:
=IF(OFFSET(Sheet1!$A$1,ROW()-1,MATCH(B$1,Sheet1!$1:$1,0))<>"",OFFSET(Sheet1!$A$1,ROW()-1,MATCH(B$1,Sheet1!$1:$1,0)),"")

Copy both formulae down until you get a blank (end of data on Sheet1).

OR

A4:
=IF(INDEX(Sheet1!$A$1:$F$100,ROW(),MATCH(Sheet2!A$1,Sheet1!$1:$1,0)*1)=0,"",INDEX(Sheet1!$A$1:$F$100,ROW(),MATCH(Sheet2!A$1,Sheet1!$1:$1,0)))


B4:
==IF(INDEX(Sheet1!$A$1:$F$100,ROW(),MATCH(Sheet2!B$1,Sheet1!$1:$1,0)+1)=0,"",INDEX(Sheet1!$A$1:$F$100,ROW(),MATCH(Sheet2!B$1,Sheet1!$1:$1,0)+1))

Range A1:F100 is the range of your data on Sheet1: change as required


Change Sheet1 to suit in both cases.

HTH

 

[Guest]

Thank you, your a genius

Hi,
Assuming the output data fomat is the same as the input then:

on OUTPUT sheet: Month in A1 and B1, "Sub" & "Adj" in A2 & B2 then in:

A4:
=IF(OFFSET(Sheet1!$A$1,ROW()-1,MATCH(A$1,Sheet1!$1:$1,0)-1)<>"",OFFSET(Sheet1!$A$1,ROW()-1,MATCH(A$1,Sheet1!$1:$1,0)-1),"")

in B4:
=IF(OFFSET(Sheet1!$A$1,ROW()-1,MATCH(B$1,Sheet1!$1:$1,0))<>"",OFFSET(Sheet1!$A$1,ROW()-1,MATCH(B$1,Sheet1!$1:$1,0)),"")

Copy both formulae down until you get a blank (end of data on Sheet1).

OR

A4:
=IF(INDEX(Sheet1!$A$1:$F$100,ROW(),MATCH(Sheet2!A$1,Sheet1!$1:$1,0)*1)=0,"",INDEX(Sheet1!$A$1:$F$100,ROW(),MATCH(Sheet2!A$1,Sheet1!$1:$1,0)))


B4:
==IF(INDEX(Sheet1!$A$1:$F$100,ROW(),MATCH(Sheet2!B$1,Sheet1!$1:$1,0)+1)=0,"",INDEX(Sheet1!$A$1:$F$100,ROW(),MATCH(Sheet2!B$1,Sheet1!$1:$1,0)+1))

Range A1:F100 is the range of your data on Sheet1: change as required


Change Sheet1 to suit in both cases.

HTH