Is it vlookup or index match?

[asad]

I got the following data on a worksheet.

Excel Workbook
	A	B	C	D	E	F
1		LOPEZ			MATAU	202
2	202	MATAU			ROZE	215
3		ADDERLY			SEBASTIAN	220
4	215	ROZE			SIPILIANO	201
5		SAMBEVSKI			VUU	217
6		SANDOVAL			BACH. R	205
7	220	SEBASTIAN			BUSTAMANTE	216
8		SHEN			FALE. M	213
9		SIMBORO			GRBA	214
10	201	SIPILIANO			LOPEZ
11		SUNTOVSKI			ADDERLY
12		TAPIA			SAMBEVSKI
13		TUSITALA			SANDOVAL
14		VITALE			SHEN
15	217	VUU			SIMBORO
16		ZOIS			SUNTOVSKI
17		ARDON			TAPIA
18	205	BACH. R			TUSITALA
19		BENCE			VITALE
20		BOGDANOVIC. D			ZOIS
21	216	BUSTAMANTE			ARDON
22		EL MASRI			BENCE
23		ESPINOZA			BOGDANOVIC. D
24	213	FALE. M			EL MASRI
25		GONZALES. A			ESPINOZA
26		GONZALES. R			GONZALES. A
27	214	GRBA			GONZALES. R
28		LAVEA			LAVEA
Sheet1

I have a formula in column E to lookup the person corressponding the number in column F and find the matching person from column B. This is working perfect.
Now I want the rest of people to show at bottom in the same order as in column B barring those people whose name is already on top. What formula can I use for this? Any help would be appreciated.

Asad

 

[T. Valko]

I got the following data on a worksheet.

Excel Workbook
	A	B	C	D	E	F
1		LOPEZ			MATAU	202
2	202	MATAU			ROZE	215
3		ADDERLY			SEBASTIAN	220
4	215	ROZE			SIPILIANO	201
5		SAMBEVSKI			VUU	217
6		SANDOVAL			BACH. R	205
7	220	SEBASTIAN			BUSTAMANTE	216
8		SHEN			FALE. M	213
9		SIMBORO			GRBA	214
10	201	SIPILIANO			LOPEZ
11		SUNTOVSKI			ADDERLY
12		TAPIA			SAMBEVSKI
13		TUSITALA			SANDOVAL
14		VITALE			SHEN
15	217	VUU			SIMBORO
16		ZOIS			SUNTOVSKI
17		ARDON			TAPIA
18	205	BACH. R			TUSITALA
19		BENCE			VITALE
20		BOGDANOVIC. D			ZOIS
21	216	BUSTAMANTE			ARDON
22		EL MASRI			BENCE
23		ESPINOZA			BOGDANOVIC. D
24	213	FALE. M			EL MASRI
25		GONZALES. A			ESPINOZA
26		GONZALES. R			GONZALES. A
27	214	GRBA			GONZALES. R
28		LAVEA			LAVEA
Sheet1

I have a formula in column E to lookup the person corressponding the number in column F and find the matching person from column B. This is working perfect.
Now I want the rest of people to show at bottom in the same order as in column B barring those people whose name is already on top. What formula can I use for this? Any help would be appreciated.

Asad

Try this...

Enter this array formula** in E10 and copy down to E28:

=INDEX(B:B,SMALL(IF(A$1:A$28="",ROW(A$1:A$28)),ROWS(E$10:E10)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[asad]

Beautiful BIFF, Thanks a lot.

Asad

 

[T. Valko]

Beautiful BIFF, Thanks a lot.

Asad

You're welcome. Thanks for the feedback!