Index / Match (if honest I'm lost) - all day spend trying to get this working

[Stoobydoo]

	A	B	C	D	E	F	G	H	I
1				Search:	ABC			DEF
2	ID	Start	Finish		Start	Finish		Start	Finish
3	ABC 123	1/01/2019	6/01/2019		1/01/2019	6/01/2019		2/01/2019	7/01/2019
4	DEF 123	2/01/2019	7/01/2019			8/07/2019		3/01/2019
5	ABC 456		8/07/2019		4/01/2019	9/07/2019		5/01/2019
6	DEF 789	3/01/2019
7	ABC 789	4/01/2019	9/07/2019
8	DEF 789	5/01/2019
9					Earliest Start	Latest Finish		Earliest Start	Latest Finish
10					1/01/2019	9/07/2019		2/01/2019	7/01/2019

Sheet: Sheet1

Hi all,

I've tried various formulas all day but always ran into one problem or another.

Basically:
Cells E3-F8 are related to Cell E1 (ABC).
Cells H3-G8 are related to Cell H1 (DEF)

The formula I need

Take E3 as an example, I need it to look at E1 find it's first partial match in Column A and return the date found in Column B.

Where it is a blank I need it return a blank (this has been an issue).

Anyone done something like this before and can help me?

[k64]

How about this?

Please Login or Register  to view this content.

[Stoobydoo]

Hi K64,

Many thanks for your reply. I tried your formula but don't think it works either.

I think your formula requires a straight match and when I copy it down it will keep finding the same date over and over?

This task is proving very difficult.

The formula I've managed to get working (except it returns 00/Jan/1900 / 0 when it doesn't find something) is:

=IFERROR(INDEX($B$1:$B$20000,SMALL(IF(ISNUMBER(SEARCH($F$1,$A$1:$A$20000)),MATCH(ROW($A$1:$A$20000),ROW($A$1:$A$20000))),ROW($A1))), "")

That formula works except when it finds a blank cell in one of the dates it returns 0 (00/Jan/1900), I need it to return BLANK too.

[Pete_UK]

You can use that formula, but you should apply a Custom Format of:

d/mm/yyyy;;

to the cells - the double semicolons at the end cause zero and negative values to be displayed as blanks.

Hope this helps.

Pete

[Stoobydoo]

Amazing! thanks.

Also, any idea why the formula appears to have a complete mind of it's own?

I am copying it, all seems well, and then it doesn't work.

Also, at one moment it seems to work, and then the next it's not.

It's all very odd!

is my beginning formula just terrible do you think?