Looking up multiple rows

[andys25]

Hi all.

This is my first post so please accept any errors i may make.

I am having trouble with lookup (I have also searched the web to look for a specific or other hints to apply to my problem).

What i am trying to do is get my sheet to auto fill from another sheet looking up for data with specific info. to be precise i want the cell in one work sheet to look in a table on another sheet for anything that has "Q1" in the first row, then replace this in the corrispondig row of the other worksheet. this i can do with the simple if statement and vlookup but it is clearly only returning the first it finds. i need it to record all entries in order. i have attached a simple example to try an help you work out what i am wanting it to do.

I want to cell in worksheet Q1 cell B:4 (and then continuosly downwards) to look in the invoices worksheet Column A for anything with Q1, then return the date in the B Column of the first Q1 found in column A to the cell in the Q1 worksheet. then the cell in worksheet Q1 cell B:5 to find the next Q1 in the A column in worksheet invoices and so on, and on etc.

I hope this makes some sence.

Andy

[FDibbins]

As a start, try using this in sheet Q1 B4, copied down...
=IF(Invoices!$A$3:$A$21="Q1",SMALL(Invoices!$C$3:$C$21,ROW(A1)),"")

it is an array formula and must be entered using ctrl shift enter, not just enter. If that is heading in the right direction for you, we can carry on and add more to what you want

let me know please

[andys25]

As a start, try using this in sheet Q1 B4, copied down...
=IF(Invoices!$A$3:$A$21="Q1",SMALL(Invoices!$C$3:$C$21,ROW(A1)),"")

it is an array formula and must be entered using ctrl shift enter, not just enter. If that is heading in the right direction for you, we can carry on and add more to what you want

let me know please

Its working of sorts, it finds the first 3 which is correct but is then generating further dates. if you can imagine i am expecting the following dates to appear in Q1 (based on current data in "invoices")

B:4 01/04/13
B:5 01/05/13
B:6 01/06/13
B:7 01/04/14
B:8 01/05/14
B:9 01/06/14
B:10