Right Function combined with vlookup

**Technofob** · 03-13-2012, 01:29 PM

I have a spreadsheet with 7 digit extensions numbers.

For example.

9838522
9838699
9838159
9838325
9838354

I would like to use the right function combined with the vlookup to return all extension numbers beginning with 83

Can somebody please advise on the correct formula i should be usinng?

**dilipandey** · 03-13-2012, 01:39 PM

Hi Technofob,

Welcome to the forum.

Basis your example, do you need :-

838522
838699
838159
838325
838354

Or do you need:-

8325
8354

or

None

as all numbers begins with 9

?

Regards,
DILIPandey

<click on below 'star' if this helps>

**Andrew-R** · 03-13-2012, 01:43 PM

If your data is in column A then try this formula in another column:

=IF(ROW(1:1)>SUMPRODUCT(--(LEFT(A$1:A$1000,2)="83")),"",INDEX(A:A,LARGE(INDEX(ROW(A$1:A$1000)*(LEFT(A$1:A$1000,2)="83"),0),SUMPRODUCT(--(LEFT(A$1:A$1000,2)="83"))-(ROW(1:1)-1))))

And drag down to fill in all of the numbers.

**Technofob** · 03-13-2012, 02:03 PM

Originally Posted by dilipandey

Hi Technofob,

Welcome to the forum.

Basis your example, do you need :-

838522
838699
838159
838325
838354

Or do you need:-

8325
8354

or

None

as all numbers begins with 9

?

Regards,
DILIPandey

<click on below 'star' if this helps>

I need 8325, 8354 etc

**dilipandey** · 03-13-2012, 02:19 PM

Hi Technofab,

See the attachment and let me know if this is what you were looking forward, if not share your sample scenario(s). thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**Technofob** · 03-13-2012, 03:18 PM

This is exactly what i need. Many Thanks

**dilipandey** · 03-13-2012, 03:20 PM

You are welcome Technofob...

Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

**ChemistB** · 03-13-2012, 03:54 PM

Andrew's formula modified for using MID instead of LEFT works and removes blanks

=IF(ROW(1:1)>SUMPRODUCT(--(MID(A$1:A$1000,4,2)="83")),"",INDEX(MID(A:A,4,4),LARGE(INDEX(ROW(A$1:A$1000)*(MID(A$1:A$1000,4,2)="83"),0),SUMPRODUCT(--(MID(A$1:A$1000,4,2)="83"))-(ROW(1:1)-1))))