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

S

sampayor

New Member
Joined
Jun 7, 2022
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
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
 

Attachments

  • pic.png
    pic.png
    58.2 KB · Views: 13

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
i mean cell.... match any cell in table range and return with value in right side of the cell..
 
Upvote 0
The requirement is not clear to me (or I guess the other 20 or so people who have looked at your question).
Perhaps you can explain in more detail and give an example?
 
Upvote 0
Peter_SSs said:
The requirement is not clear to me (or I guess the other 20 or so people who have looked at your question).
Perhaps you can explain in more detail and give an example?
Click to expand...
let me explain further, i have 2 worksheet..1st sheet is for data entry named "test" and 2nd sheet for 'info'..if i key in any text that match in the table range in sheet 'info', it will return the value in the right side of cell...for example, if i keyin KP19, the result will be Pegawai Imigresen (PI). if i keyin (Pegawai Imigresen (KP19), the result will be E29....if i key in F54, the result wil be Pengarah Teknologi Maklumat..

sheet 'test':
demo.xlsm
AB
3GredJawatan
4E32#N/A
test
Cell Formulas
RangeFormula
B4B4=MATCH(A4,tblJawatan,0)


sheet'info':
demo.xlsm
ABCDEF
1ImigresenImigresen2EkonomiEkonomi2Sistem MaklumatSistem Maklumat2
2KP19PEGAWAI IMIGRESEN (PI)E29Penolong Pegawai PerangkaanFT19Juruteknik Komputer
3KP22PEGAWAI IMIGRESEN KANAN (PIK)E32Penolong Pegawai PerangkaanFT22Juruteknik Komputer
4KP26PEGAWAI IMIGRESEN TINGGI (PIT)E36Penolong Pegawai PerangkaanFA29Penolong Pegawai Teknologi Maklumat
5KP28KETUA PEGAWAI IMIGRESEN TINGGI (K/PIT)FA32Penolong Pegawai Teknologi Maklumat
6KP29TIMBALAN PENOLONG PENGARAH IMIGRESEN (TPPI)FA38Penolong Pegawai Teknologi Maklumat
7KP32TIMBALAN PENOLONG PENGARAH IMIGRESEN KANAN (TPPK)F41Pegawai Teknologi Maklumat
8KP38KETUA TIMBALAN PENOLONG PENGARAH IMIGRESEN (KTPP)F44Pegawai Teknologi Maklumat
9KP41PENOLONG PENGARAH IMIGRESEN F48Pegawai Teknologi Maklumat
10KP42PENOLONG PENGARAH IMIGRESEN F52Timbalan Pengarah Teknologi Maklumat
11KP44PENOLONG PENGARAH IMIGRESEN KANANF54Pengarah Teknologi Maklumat
12KP48KETUA PENOLONG PENGARAH IMIGRESEN
13KP52PENGARAH BAHAGIAN /
14KP54PENGARAH BAHAGIAN /
15
16
17
18
19
20
21
info
 
Upvote 0
Thanks for the extra information and XL2BB samples

sampayor said:
if i keyin (Pegawai Imigresen (KP19), the result will be E29
Click to expand...
  1. That does not appear in the table. Did you mean PEGAWAI IMIGRESEN (PI) ?

  2. What should happen if you key in Penolong Pegawai Perangkaan given that there are three of those in the table, all with different values to the right?
 
Upvote 0
Peter_SSs said:
Thanks for the extra information and XL2BB samples


  1. That does not appear in the table. Did you mean PEGAWAI IMIGRESEN (PI) ?

  2. What should happen if you key in Penolong Pegawai Perangkaan given that there are three of those in the table, all with different values to the right?
Click to expand...
1. yup..
2. sorry my mistake.it should be unique..i upload again sheet 'info'.

demo.xlsm
ABCDEF
1ImigresenImigresen2EkonomiEkonomi2Sistem MaklumatSistem Maklumat2
2KP19PEGAWAI IMIGRESEN (PI)E29Penolong Pegawai Perangkaan1FT19Juruteknik Komputer1
3KP22PEGAWAI IMIGRESEN KANAN (PIK)E32Penolong Pegawai Perangkaan2FT22Juruteknik Komputer2
4KP26PEGAWAI IMIGRESEN TINGGI (PIT)E36Penolong Pegawai Perangkaan3FA29Penolong Pegawai Teknologi Maklumat1
5KP28KETUA PEGAWAI IMIGRESEN TINGGI (K/PIT)FA32Penolong Pegawai Teknologi Maklumat2
6KP29TIMBALAN PENOLONG PENGARAH IMIGRESEN (TPPI)FA38Penolong Pegawai Teknologi Maklumat3
7KP32TIMBALAN PENOLONG PENGARAH IMIGRESEN KANAN (TPPK)F41Pegawai Teknologi Maklumat1
8KP38KETUA TIMBALAN PENOLONG PENGARAH IMIGRESEN (KTPP)F44Pegawai Teknologi Maklumat2
9KP41PENOLONG PENGARAH IMIGRESEN 1F48Pegawai Teknologi Maklumat3
10KP42PENOLONG PENGARAH IMIGRESEN 2F52Timbalan Pengarah Teknologi Maklumat
11KP44PENOLONG PENGARAH IMIGRESEN KANANF54Pengarah Teknologi Maklumat
12KP48KETUA PENOLONG PENGARAH IMIGRESEN
13KP52PENGARAH BAHAGIAN 1
14KP54PENGARAH BAHAGIAN 2
info
 
Upvote 0
additional info: if i key in Pengarah Bahagian 2 (refer to B14), it should return blank...
 
Upvote 0
It will be better if using VBA code.
Below is formula solution:

Book1
ABCDEF
1ImigresenImigresen2EkonomiEkonomi2Sistem MaklumatSistem Maklumat2
2KP19PEGAWAI IMIGRESEN (PI)E29Penolong Pegawai Perangkaan1FT19Juruteknik Komputer1
3KP22PEGAWAI IMIGRESEN KANAN (PIK)E32Penolong Pegawai Perangkaan2FT22Juruteknik Komputer2
4KP26PEGAWAI IMIGRESEN TINGGI (PIT)E36Penolong Pegawai Perangkaan3FA29Penolong Pegawai Teknologi Maklumat1
5KP28KETUA PEGAWAI IMIGRESEN TINGGI (K/PIT)FA32Penolong Pegawai Teknologi Maklumat2
6KP29TIMBALAN PENOLONG PENGARAH IMIGRESEN (TPPI)FA38Penolong Pegawai Teknologi Maklumat3
7KP32TIMBALAN PENOLONG PENGARAH IMIGRESEN KANAN (TPPK)F41Pegawai Teknologi Maklumat1
8KP38KETUA TIMBALAN PENOLONG PENGARAH IMIGRESEN (KTPP)F44Pegawai Teknologi Maklumat2
9KP41PENOLONG PENGARAH IMIGRESEN 1F48Pegawai Teknologi Maklumat3
10KP42PENOLONG PENGARAH IMIGRESEN 2F52Timbalan Pengarah Teknologi Maklumat
11KP44PENOLONG PENGARAH IMIGRESEN KANANF54Pengarah Teknologi Maklumat
12KP48KETUA PENOLONG PENGARAH IMIGRESEN
13KP52PENGARAH BAHAGIAN 1
14KP54PENGARAH BAHAGIAN 2
info

Book1
ABC
1
2
3OPTION 1OPTION 2
4E32Penolong Pegawai Perangkaan2Penolong Pegawai Perangkaan2
5KP19PEGAWAI IMIGRESEN (PI)PEGAWAI IMIGRESEN (PI)
6PEGAWAI IMIGRESEN TINGGI (PIT)E36E36
7PENGARAH BAHAGIAN 20 
TEST
Cell Formulas
RangeFormula
B4:B7B4=INDIRECT("info!"&ADDRESS(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:C7C4=IF(INDIRECT("info!"&ADDRESS(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)))="","",INDIRECT("info!"&ADDRESS(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))))
 
Upvote 0
Or, non volatile option (non INDIRECT, ADRRESS)
Book1
ABC
1
2
3OPTION 1OPTION 2
4E32Penolong Pegawai Perangkaan2Penolong Pegawai Perangkaan2
5KP19PEGAWAI IMIGRESEN (PI)PEGAWAI IMIGRESEN (PI)
6PEGAWAI IMIGRESEN TINGGI (PIT)E36E36
7PENGARAH BAHAGIAN 20 
TEST
Cell Formulas
RangeFormula
B4:B7B4=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:C7C4=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)))
 
Upvote 0
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
ABCDEF
1ImigresenImigresen2EkonomiEkonomi2Sistem MaklumatSistem Maklumat2
2KP19PEGAWAI IMIGRESEN (PI)E29Penolong Pegawai Perangkaan1FT19Juruteknik Komputer1
3KP22PEGAWAI IMIGRESEN KANAN (PIK)E32Penolong Pegawai Perangkaan2FT22Juruteknik Komputer2
4KP26PEGAWAI IMIGRESEN TINGGI (PIT)E36Penolong Pegawai Perangkaan3FA29Penolong Pegawai Teknologi Maklumat1
5KP28KETUA PEGAWAI IMIGRESEN TINGGI (K/PIT)FA32Penolong Pegawai Teknologi Maklumat2
6KP29TIMBALAN PENOLONG PENGARAH IMIGRESEN (TPPI)FA38Penolong Pegawai Teknologi Maklumat3
7KP32TIMBALAN PENOLONG PENGARAH IMIGRESEN KANAN (TPPK)F41Pegawai Teknologi Maklumat1
8KP38KETUA TIMBALAN PENOLONG PENGARAH IMIGRESEN (KTPP)F44Pegawai Teknologi Maklumat2
9KP41PENOLONG PENGARAH IMIGRESEN 1F48Pegawai Teknologi Maklumat3
10KP42PENOLONG PENGARAH IMIGRESEN 2F52Timbalan Pengarah Teknologi Maklumat
11KP44PENOLONG PENGARAH IMIGRESEN KANANF54Pengarah Teknologi Maklumat
12KP48KETUA PENOLONG PENGARAH IMIGRESEN
13KP52PENGARAH BAHAGIAN 1
14KP54PENGARAH BAHAGIAN 2
info


sampayor.xlsm
AB
3GredJawatan
4Penolong Pegawai Perangkaan1FT19
5E32Penolong Pegawai Perangkaan2
6KP44PENOLONG PENGARAH IMIGRESEN KANAN
7ABCD 
8FA29Penolong Pegawai Teknologi Maklumat1
9Pengarah Bahagian 2 
test
Cell Formulas
RangeFormula
B4:B9B4=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)&"","")
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

E
  • Question
can't get INDEX-MATCH to copy entire column
Replies
2
Views
133
shift-del
shift-del
J
  • Solved
Finding Closest match in a range
Replies
8
Views
261
Fluff
Fluff
S
  • Question
Macro to add sum total to track percentages
Replies
0
Views
165
spenserallen7
S
C
  • Question
Hlookup with range of values
Replies
3
Views
120
Alex Blakenburg
A
E
  • Solved
This one is tricky. VLookup but return a different cell value
Replies
6
Views
391
Alex Blakenburg
A

Forum statistics

Threads
1,216,523
Messages
6,131,151
Members
449,626
Latest member
Stormythebandit

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top