Return value of first and last occurence

[mrstrent]

I cant figure out two formulas...
-non macro...

1st: Will return the date of first occurence of E2 (Will equal 9/3/2011)
*will return the value in "A", for first occurence in B2:D9

2nd: Will return the date of the last occurence of E2 (Will equal 9/8/2011)
*will return the value in "A", for last occurence in B2:D9

1 A B C D E
2 Date: Code 1 Code 2 Code 3 1066
3 9/1/2011 5154 5156 5160
4 9/2/2011 5313 5224 5235
5 9/3/2011 1066 2135 2378
6 9/4/2011 2090 5225 1066
7 9/5/2011 5153 5156 5263
8 9/6/2011 5227 5313 5233
9 9/7/2011 1066 2090 2135
10 9/8/2011 1066 2075 2280

-see attached spread sheet.

Thank you in advance for the help!

[ChemistB]

For first occurance

=INDEX($A$2:$A$9,MATCH($E$1,$B$2:$B$9,0))

For last occurance
=LOOKUP(2,1/($B$2:$B$9=E1),$A$2:$A$9)
Does that work for you?

[mrstrent]

that does work for my first code... but my problem is that i need 3 codes.

So the formula would have to be:
First:
=INDEX($A$2:$A$9,MATCH($E$1,$B$2:$D$9,0))
-when the match is a "Range of B:D" it shows #N/A

Last:
=LOOKUP(2,1/($B$2:$D$9=E1),$A$2:$A$9)
when the match is a "Range of B:D" it shows #N/A

[ChemistB]

Yes, MATCH and LOOKUP work for single columns/rows. One option is

MIN(INDEX($A$2:$A$9,MATCH($E$1,$B$2:$B$9,0)),INDEX($A$2:$A$9,MATCH($E$1,$C$2:$C$9,0)),INDEX($A$2:$A$9,MATCH($E$1,$D$2:$D$9,0)))

and

MAX(LOOKUP(2,1/($B$2:$B$9=E1),$A$2:$A$9),LOOKUP(2,1/($C$2:$C$9=E1),$A$2:$A$9),LOOKUP(2,1/($D$2:$D$9=E1),$A$2:$A$9))

Maybe someone will come up with a more elegant formula.

[mrstrent]

doesnt work for me...

it appears that the formulas:

MIN(INDEX($A$2:$A$9,MATCH($E$1,$B$2:$B$9,0)),INDEX($A$2:$A$9,MATCH($E$1,$C$2:$C$9,0)),INDEX($A$2:$A$ 9,MATCH($E$1,$D$2:$D$9,0)))

and

MAX(LOOKUP(2,1/($B$2:$B$9=E1),$A$2:$A$9),LOOKUP(2,1/($C$2:$C$9=E1),$A$2:$A$9),LOOKUP(2,1/($D$2:$D$9=E1),$A$2:$A$9))

Work; but if the value in "E1" is not present in any of the ranges (ie: "B2:B9", "C2:C9", "D2:D9") the entire formula will result in #N/A.

is there a way to suppress the #N/A in the error portion of the search?

[ChemistB]

Ack, I didn't test before submitting. This makes it more complex because you need to test before each INDEX. For example, the MIN equation becomes

=MIN(IF(ISERROR(MATCH($E$1,$B$2:$B$9,0)),99^100,INDEX($A$2:$A$9,MATCH($E$1,$B$2:$B$9,0))),IF(ISERROR(MATCH($E$1,$C$2:$C$9,0)),99^100,INDEX($A$2:$A$9,MATCH($E$1,$C$2:$C$9,0))),IF(ISERROR(MATCH($E$1,$D$2:$D$9,0)),99^100,INDEX($A$2:$A$9,MATCH($E$1,$D$2:$D$9,0))))

I'll ask around for someone to simplify.

[tigeravatar]

mrstrent,

As long as you don't mind array formulas you can use the following:

Formula to find the first occurrence:
=IF(COUNTIF($B$2:$D$9,$E$1)=0,$E$1&" not found",INDEX($A$2:$A$9,MIN(IF($B$2:$D$9=$E$1,ROW($B$2:$D$9)-1,65000))))

Formula to find the last occurrence:
=IF(COUNTIF($B$2:$D$9,$E$1)=0,$E$1&" not found",INDEX($A$2:$A$9,MAX(IF($B$2:$D$9=$E$1,ROW($B$2:$D$9)-1,0))))

Both confirmed with Ctrl+Shift+Enter (not just Enter). Attached is a modified version of your sample workbook so you can see how it works

Hope that helps,
~tigeravatar

[daddylonglegs]

You could use this formula for the earliest date

=MIN(IF(B2:D9=E1,A2:A9))

confirmed with CTRL+SHIFT+ENTER

custom format result cell as dd/mm/yy;; [note the two semi-colons] so that if E1 isn't found in B2:D9 the result cell will display a blank. For the latest date change MIN to MAX

I'm assuming the dates are in chronological order so the "first date" positionally = the earliest date etc....

[mrstrent]

Perfect, thanks for all the help...

I ended up altering a version of tigeravatars.

Thank you all!