Formula to find next value

[kelbarben]

Is there a formula that will find the next matching text in a column and return the text from the column to the right of it? Like a vlookup but I need it to continue on and find every one of them and return each thing that matches that row that the next one shows up in. Thanks in advance.

kelbarben

 

[al_b_cnu]

Hi,

an alternative using a 'Helper Column' might be:

Excel Workbook
	A	B
1	Service Agent	Account code
2	Amy	178168
3	Teri	183302
4	Bob	184881
5	Teri	191562
6	Alex	199567
7	Jake	199747
8	Sam	208945
9	Sharon	212520
10	Jake	218960
11	Lowell	220245
12	Greg	220248
13	Emily	220250
14	Bob	220251
15	Bob	220253
16	Amy	220254
17	Teri	220255
18	Alex	220256
19	Amber	220259
20	Bob	220262
21	Jake	220263
22	Amy	220264
23	Amy	220268
24	Amber	220274
25	Bob	220275
26	Bob	220276
27	Amy	176876
28	Alex	181504
29	Alex	198347
Sheet1

Excel Workbook
	A	B	C
1		Helper Column	Account number
2	Amy	2	178168
3		16	220254
4		22	220264
5		23	220268
6		27	176876
7		#N/A	#N/A
8		#N/A	#N/A
Sheet2

 

[bosco_yip]

1] Please followed al_b_cnu's Sheet 1 layout

2] Sheet2, A2 entered : AMY

3] Sheet2, B2 entered array formula and copied down :

{=INDEX(Sheet1!B$1:B$1000,SMALL(IF(Sheet1!A$1:A$1000=$A$2,ROW(B$1:B$1000),1000),ROWS(A$1:A1)))&""}

Regards
Bosco