How to match any column in table range and return with value in right side of the column?

[sampayor]

i have 1 large table range with various data.my intention is to match any column in table range and return with value in right side of the column? most common formula is to use vlookup or match,index but it specific with column or row only..i attach part of table range pic...hope anyone can help me.tqsm

 

[sampayor]

Or, non volatile option (non INDIRECT, ADRRESS)

Book1
	A	B	C
1
2
3		OPTION 1	OPTION 2
4	E32	Penolong Pegawai Perangkaan2	Penolong Pegawai Perangkaan2
5	KP19	PEGAWAI IMIGRESEN (PI)	PEGAWAI IMIGRESEN (PI)
6	PEGAWAI IMIGRESEN TINGGI (PIT)	E36	E36
7	PENGARAH BAHAGIAN 2	0
TEST

Cell Formulas
Range	Formula
B4:B7	B4	=INDEX(info!$A$1:$F$14,AGGREGATE(14,6,ROW(info!$B$2:$F$14)/(info!$A$2:$E$14=A4),1),AGGREGATE(14,6,COLUMN(info!$B$2:$F$14)/(info!$A$2:$E$14=A4),1))
C4:C7	C4	=IF(INDEX(info!$A$1:$F$14,AGGREGATE(14,6,ROW(info!$B$2:$F$14)/(info!$A$2:$E$14=A4),1),AGGREGATE(14,6,COLUMN(info!$B$2:$F$14)/(info!$A$2:$E$14=A4),1))="","",INDEX(info!$A$1:$F$14,AGGREGATE(14,6,ROW(info!$B$2:$F$14)/(info!$A$2:$E$14=A4),1),AGGREGATE(14,6,COLUMN(info!$B$2:$F$14)/(info!$A$2:$E$14=A4),1)))

tq bebo021999 for your solution..i already try and it successful....but 1 problem is the table in sheet 'info' is in table range and act as reference table, means that the row and column will be expand from time to time...

 

[sampayor]

I would go for a more robust solution that does not return incorrect results if any rows happen to subsequently be inserted above the table on the 'info' sheet.
Also, since that data appears to be in a formal table, I would utilise the table nomenclature so that formula ranges do not need to be adjusted if the table changes size.

I have assumed the table on 'info' is called table1. Adjust the formula if it has a different name.

sampayor.xlsm
	A	B	C	D	E	F
1	Imigresen	Imigresen2	Ekonomi	Ekonomi2	Sistem Maklumat	Sistem Maklumat2
2	KP19	PEGAWAI IMIGRESEN (PI)	E29	Penolong Pegawai Perangkaan1	FT19	Juruteknik Komputer1
3	KP22	PEGAWAI IMIGRESEN KANAN (PIK)	E32	Penolong Pegawai Perangkaan2	FT22	Juruteknik Komputer2
4	KP26	PEGAWAI IMIGRESEN TINGGI (PIT)	E36	Penolong Pegawai Perangkaan3	FA29	Penolong Pegawai Teknologi Maklumat1
5	KP28	KETUA PEGAWAI IMIGRESEN TINGGI (K/PIT)			FA32	Penolong Pegawai Teknologi Maklumat2
6	KP29	TIMBALAN PENOLONG PENGARAH IMIGRESEN (TPPI)			FA38	Penolong Pegawai Teknologi Maklumat3
7	KP32	TIMBALAN PENOLONG PENGARAH IMIGRESEN KANAN (TPPK)			F41	Pegawai Teknologi Maklumat1
8	KP38	KETUA TIMBALAN PENOLONG PENGARAH IMIGRESEN (KTPP)			F44	Pegawai Teknologi Maklumat2
9	KP41	PENOLONG PENGARAH IMIGRESEN 1			F48	Pegawai Teknologi Maklumat3
10	KP42	PENOLONG PENGARAH IMIGRESEN 2			F52	Timbalan Pengarah Teknologi Maklumat
11	KP44	PENOLONG PENGARAH IMIGRESEN KANAN			F54	Pengarah Teknologi Maklumat
12	KP48	KETUA PENOLONG PENGARAH IMIGRESEN
13	KP52	PENGARAH BAHAGIAN 1
14	KP54	PENGARAH BAHAGIAN 2
info

sampayor.xlsm
	A	B
3	Gred	Jawatan
4	Penolong Pegawai Perangkaan1	FT19
5	E32	Penolong Pegawai Perangkaan2
6	KP44	PENOLONG PENGARAH IMIGRESEN KANAN
7	ABCD
8	FA29	Penolong Pegawai Teknologi Maklumat1
9	Pengarah Bahagian 2
test

Cell Formulas
Range	Formula
B4:B9	B4	=IFERROR(INDEX(Table1,AGGREGATE(15,6,(ROW(Table1)-ROW(Table1[#Headers]))/(Table1=A4),1),AGGREGATE(15,6,(COLUMN(Table1)-COLUMN(INDEX(Table1[#Headers],1))+1)/(Table1=A4),1)+1)&"","")

Tq so much Peter_SSs. definitely..solved my case...tqsm again..​

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.