Find the first and last value for the given input.

[Sekars]

Hello Friends

Please find the attached file.

I need to find the first and last value for the given input (D2:D3) in light yellow cells (E2:F3).

thanks in advance

[FDibbins]

For the 1st value, try this, copied down...
=VLOOKUP(D2,$A$1:$B$26,2,0)
Will the list always be sorted like that?

[FDibbins]

Slight modification.

1st entry...
=INDEX($B$1:$B$26,MATCH($D2,$A$1:$A$26,0))
2nd entry, assuming they are sorted...
=INDEX($B$1:$B$26,MATCH($D2,$A$1:$A$26,0)+COUNTIF($A$1:$A$26,D2)-1)
both copied down as needed

[FlameRetired]

Another way.

Please Login or Register  to view this content.

[Phuocam]

Or try this ...
2nd entry
=LOOKUP(2,1/($A$1:$A$26=$D2),$B$1:$B$26)

[FlameRetired]

Or this array-entered in E2 filled down and across.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

[AlKey]

Or this
Enter in E2, copy across and down

Please Login or Register  to view this content.

[FlameRetired]

Wow!@! Nice one, AlKey!!! What a lesson. That's beautiful. Couldn't quite figure out where you were going with this one at first. LOL

I guess I need to start playing with AGGREGATE more.

[Sekars]

Hello Ford, Hello Dave, Hello Phuocam, Hello Alkey

Thanks for all of you. All of your formulas working well.

thanks again

[FDibbins]

Happy to help

[FlameRetired]

You're welcome. Thanks for the feedback.