Index/Match question

[PhilH1982]

In a sheet I have an index/match formula that looks at my ten oldest dates and pulls through the related data.

However, if I have say 5 entries for the same date, it will only pull through the first entry on the list that it comes to before moving onto the next date.

Is there a way around where I get the sheet to then look at the next entry with a matching date?

Formula for looking at oldest dates

=IF(ISERROR(SMALL(TB014!$D$2:$D$5000,ROW(A1))),"",SMALL(TB014!$D$2:$D$5000,ROW(A1)))

Formula to match related info

=IF(ISERROR(INDEX(TB014!C$2:C$5000,MATCH($C2,TB014!$D$2:$D$5000,0))),"",INDEX(TB014!C$2:C$5000,MATCH($C2,TB014!$D$2:$D$5000,0)))

[Lotus123]

For the purposes of my answer I must make an assumption on where your data is located. You will need to change this assumption as needed.

Assumption #1:
=IF(ISERROR(SMALL(TB014!$D$2:$D$5000,ROW(A1))),"", SMALL(TB014!$D$2:$D$5000,ROW(A1)))

I assume the above 10 similar formulas are located in cells A1:A10. I use them in the below COUNTIF that starts at A1.

Now here is the new INDEX formula. This is an ARRAY formula and must be confirmed with CTRL-SHIFT-ENTER:

=IF(ISERROR(INDEX(C$2:C$5000,SMALL(IF($D$2:$D$5000=A1,ROW($D$2:$D$5000)-1),COUNTIF(OFFSET($A$1,0,0,ROW(),1),A1)))),"",INDEX(C$2:C$5000,SMALL(IF($D$2:$D$5000=A1,ROW($D$2:$D$5000)-1),COUNTIF(OFFSET($A$1,0,0,ROW(),1),A1))))

Please post here if you have any additional questions.

[PhilH1982]

I cant get it to pull the data.

From reading your formula, I dont quite understand it.

The way my sheet is set up, column C is the oldest dates column.

The formula you showed me should look at the date in column C and match the data in sheet "TB014".

I want column A to pull the matched data from sheet "TB014" column A

Column B to pull data matched from sheet "TB014" column D.

I know i may have made that confusing but if you can show where the sheet name goes that would be great.

[Lotus123]

With your exact settings:

Please Login or Register  to view this content.

I have also enclosed a spreadsheet example.

[PhilH1982]

Thanks for that.

I'll inform tomorrow if it works properly for me when I'm back at work.I'm at home now and I only have Star Office. The formula for pulling the matching data doesnt work on this version so I'm assuming that its because its not a function in Star Office.

[PhilH1982]

Well the formula works fantastic to a point but I think that was my bad description.

If there are more than 1 matching dates it pulls all records below the first entry.

I need the formula to pull all entries with a matching date.

Thanks for the help so far, I just need this formula tuned slightly.

[Lotus123]

It appears I still do not understand - the formula does pull all entries with a matching date. Perhaps you can send me an example of where it does not work? Perhaps give me a sample input and what the output should look like.

[PhilH1982]

Ok, unfortunately I cant post an example, but I'll try my best to explain further.

At present with the formula you provided, if I have say 5 entries matching 1 November 2007, it will skip the first entry and display the following 4 but the final entry will be duplicated.

If there are two entries with the same date, it will put the second entries details in twice.

If there is only one matching date, it pulls through fine.

[Dav]

The formuala works for me in the example given. However in the TB014 sheet the data starts in row 2 and in the Data sheet it starts in row1, it is important in the tb014 sheet that it starts in row 2 aqnd th eformual are array formulas, you are entering them with shift control and enter

Does that help

Regards

Dav

[PhilH1982]

Nope not working. I have entered the formula using the shift+ctrl+enter and that works, but with the amount of entries on my actual sheet, it doesnt seem to like multiple entries.

The original formula would only pull the first occurance of a matched date.

The second formula seems to jump the first occurance of a match if there are multiple matches.

05/09/2006 - COTTER (but should show the name Brassil)

05/09/2006 - COTTER

If there was another date of 05/09/2006 the next name in the list would appear.

The old formula would continously show Brassil for 05/09/2006

[PhilH1982]

Ok I've cracked it. Simple mistake by me. I deleted the row header and its solved it.

Thanks folks for the help. I'd be lost without you all