Text lookup array and match specific data
 

I am currently creating an Excel spreadsheet that looks up a city name and
returns the date from a different worksheet and gives me the ability to meet
a specific date month.
Worksheet 1

--Conditions--
City Name eg BRISBANE
Date: February

I want the formula to transpose the data for each row I have with the same
formula.

ie

A1 = BRISBANE
A2 = February 2008
Results
A5= 12 Feb 08
A6 = 26 Feb 08
A7= continues if data suits date condition and city condition.

Worksheet 2 sample data
eg columns and specific data
BRISBANE SYDNEY GOLD COAST
12-Feb 11-Feb 5-Feb
26-Feb 25-Feb 19-Feb
11-Mar 10-Mar 4-Mar
25-Mar 25-Mar 18-Mar
8-Apr 7-Apr 1-Apr
22-Apr 21-Apr 15-Apr

I would much appreciate some assistance with this query. I thought that I
could use the HLOOKUP feature, but I can't work out how to meet the
conditions and have it travel down the column as well.

Cheers

Fiona

Text lookup array and match specific data
 

Hi Fiona

If I understand you correctly, then I think the following works
Enter in cell A5 of Sheet1

=IF(MONTH(INDEX(Sheet2!$A$1:$E$11,ROW(A2),
MATCH(A$1,Sheet2!$A$1:$E$1,0)))<MONTH(A$2),"",
INDEX(Sheet2!$A$1:$E$11,ROW(A2),MATCH(A$1,Sheet2!$ A$1:$E$1,0)))

(Note I have wrapped the formula deliberately to prevent your newsreader
throwing breaks in awkward places. The formula is really continuous on one
line)

This assumes that the value in A2 of Sheet1 is an Excel date formatted as
mmmm yyyy, and not a Text entry of February 2008
Change the range to suit
Copy down as far as required

You might find it less "unwieldy" and easier to understand if you create
some named ranges first, e.g.
InsertNameDefine Name myTable Refers to =Sheet2!$A$1:$E$11 (or
whatever is appropriate)
InsertNameDefine Name Cities Refers to =Sheet2!$A$1:$E$1
InsertNameDefine Name myCity Refers to =Sheet1!$A$1:$E$1
InsertNameDefine Name myMonth Refers to =Sheet1!$A$2:$E$2

The formula then becomes
=IF(MONTH(INDEX(myTable,ROW(A2),
MATCH(myCity,Cities,0)))<MONTH(myMonth),"",
INDEX(myTable,ROW(A2),MATCH(myCity,Cities,0)))

The ROW() function returns 2, when it is ROW(A2), and this number will
increment automatically as you copy down sheet 1
You can also copy either formula across sheet1 if you wanted to have
multiple columns of data on Sheet1
--


Regards
Roger Govier

"Fiona" wrote in message
...
I am currently creating an Excel spreadsheet that looks up a city name and
returns the date from a different worksheet and gives me the ability to
meet
a specific date month.
Worksheet 1

--Conditions--
City Name eg BRISBANE
Date: February

I want the formula to transpose the data for each row I have with the same
formula.

ie

A1 = BRISBANE
A2 = February 2008
Results
A5= 12 Feb 08
A6 = 26 Feb 08
A7= continues if data suits date condition and city condition.

Worksheet 2 sample data
eg columns and specific data
BRISBANE SYDNEY GOLD COAST
12-Feb 11-Feb 5-Feb
26-Feb 25-Feb 19-Feb
11-Mar 10-Mar 4-Mar
25-Mar 25-Mar 18-Mar
8-Apr 7-Apr 1-Apr
22-Apr 21-Apr 15-Apr

I would much appreciate some assistance with this query. I thought that I
could use the HLOOKUP feature, but I can't work out how to meet the
conditions and have it travel down the column as well.

Cheers

Fiona

Text lookup array and match specific data
 

You might like this format.
Few or no formulas needed.
http://www.freefilehosting.net/download/3alme

Text lookup array and match specific data
 

Roger

Thank you for your detailed response. I will give it a go and let you know.

Cheers

Fiona

"Roger Govier" wrote:

Hi Fiona

If I understand you correctly, then I think the following works
Enter in cell A5 of Sheet1

=IF(MONTH(INDEX(Sheet2!$A$1:$E$11,ROW(A2),
MATCH(A$1,Sheet2!$A$1:$E$1,0)))<MONTH(A$2),"",
INDEX(Sheet2!$A$1:$E$11,ROW(A2),MATCH(A$1,Sheet2!$ A$1:$E$1,0)))

(Note I have wrapped the formula deliberately to prevent your newsreader
throwing breaks in awkward places. The formula is really continuous on one
line)

This assumes that the value in A2 of Sheet1 is an Excel date formatted as
mmmm yyyy, and not a Text entry of February 2008
Change the range to suit
Copy down as far as required

You might find it less "unwieldy" and easier to understand if you create
some named ranges first, e.g.
InsertNameDefine Name myTable Refers to =Sheet2!$A$1:$E$11 (or
whatever is appropriate)
InsertNameDefine Name Cities Refers to =Sheet2!$A$1:$E$1
InsertNameDefine Name myCity Refers to =Sheet1!$A$1:$E$1
InsertNameDefine Name myMonth Refers to =Sheet1!$A$2:$E$2

The formula then becomes
=IF(MONTH(INDEX(myTable,ROW(A2),
MATCH(myCity,Cities,0)))<MONTH(myMonth),"",
INDEX(myTable,ROW(A2),MATCH(myCity,Cities,0)))

The ROW() function returns 2, when it is ROW(A2), and this number will
increment automatically as you copy down sheet 1
You can also copy either formula across sheet1 if you wanted to have
multiple columns of data on Sheet1
--


Regards
Roger Govier

"Fiona" wrote in message
...
I am currently creating an Excel spreadsheet that looks up a city name and
returns the date from a different worksheet and gives me the ability to
meet
a specific date month.
Worksheet 1

--Conditions--
City Name eg BRISBANE
Date: February

I want the formula to transpose the data for each row I have with the same
formula.

ie

A1 = BRISBANE
A2 = February 2008
Results
A5= 12 Feb 08
A6 = 26 Feb 08
A7= continues if data suits date condition and city condition.

Worksheet 2 sample data
eg columns and specific data
BRISBANE SYDNEY GOLD COAST
12-Feb 11-Feb 5-Feb
26-Feb 25-Feb 19-Feb
11-Mar 10-Mar 4-Mar
25-Mar 25-Mar 18-Mar
8-Apr 7-Apr 1-Apr
22-Apr 21-Apr 15-Apr

I would much appreciate some assistance with this query. I thought that I
could use the HLOOKUP feature, but I can't work out how to meet the
conditions and have it travel down the column as well.

Cheers

Fiona