Data Lookup

IM_Haries

New Member
Joined
Nov 15, 2015
Messages
7
Hello everyone


Let Me Introduce myself, i'm Harris and work on the university as academic administration.


i want to ask a question on here


Can excell looking Variable A and B in one row,


if met Variable A and B in the same row,
then retrieve the necessary data from the database sheet in the other sheet.


Can we create Excel formulas like that? I was very frustrated and confused.


i was looking for the formula on google but i was found nothing, then i found this forum,




I also provided screenshots for ease of friends in the forum to analyze the case.



picture 1. the sheet where to put the formula.


Picture. 2 it is the database sheet.



The formula that I need is, the formula to find "Kode_MK" and "NPM" (student identification number), and if successfully found the "NPM" and "MK code" of the same in a row, then search for "Mata_Kuliah" (Name of courses)


that is it, im sorry if my english so bad and will make you all confusing, :(

And Thank you for views and replies
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,931
Hi Haries, and welcome to the board!
It will be of help if you give a simple description of your data. E.g. column A -ID numbers, Column D - can contain same ID number, Column E -Course name etc. I want to match cells A and D on the same row. If it matches then....
It will help us to understand what you need
All the best,
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
Hi Harris, it would be better if you could post a sample of your data instead of an image, see my signature for how to.
 

IM_Haries

New Member
Joined
Nov 15, 2015
Messages
7
Hi Haries, and welcome to the board!
It will be of help if you give a simple description of your data. E.g. column A -ID numbers, Column D - can contain same ID number, Column E -Course name etc. I want to match cells A and D on the same row. If it matches then....
It will help us to understand what you need
All the best,



Hi Harris, it would be better if you could post a sample of your data instead of an image, see my signature for how to.

Thanks EliW & gaz_chops ,

im so sorry if my data is so confusing (because the sheet & my language off course :) )
i will make this more simple to understand later, but in this time, im so confusing to finishing other deadline,

when im home, i will do this ASAP thx everyone :)
 

IM_Haries

New Member
Joined
Nov 15, 2015
Messages
7

ADVERTISEMENT

Excel 2007
ABCDEFGH
1BB100508
2NPMNAMAMATA KULIAHKODE MATA KULIAHDOSENSEMESTERKELASPA
32011 1 0217 008a
42010 1 0215 046b
52012 1 0215 210c
62012 1 0215 323d
72012 1 0215 324e
82012 1 0215 326f

<tbody>
</tbody>
final_sheet


Excel 2007
BCDEFGHIJK
1NPMNAMAKD_MTKMATA KULIAHSKSDOSENSEMESTERKELASPAKeterangan
22011 1 0217 008aBB100508Hukum Perindustrian2xxxVxxx
32010 1 0215 046bBB100609Komunikasi Bisnis2xxx.XP2KxxxSem VI
42012 1 0215 210cBB100609Komunikasi Bisnis2xxx.VIP2Kxxx
52012 1 0215 323dBB100609Komunikasi Bisnis2xxxVIP2Kxxx
62012 1 0215 324eBB100609Komunikasi Bisnis2xxx.VIP2Kxxx
72012 1 0215 326fBB100609Komunikasi Bisnis2xxxVIP2Kxxx

<tbody>
</tbody>
db-sheet


Here it is,

it is simple (but make me confused
:confused: )

i just want to fill the blank, and the data is from db-sheet

the logic is.... if found C2 (final sheet) and A3 (final sheet) on db_sheet, so finding A3 in db-sheet in the coloumn E,


i've tried to created the formula by myself, but im not so smart to found it,
Please everybody help me.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,050
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Make sure the sheet name is exactly correct and most importantly you enter the formula without the curly brackets and then commit the formula with Ctrl Shift + Enter and not just Enter. If done correctly Excel will add the curly brackets. Do not try entering them manually.

Excel Workbook
ABCDEFGH
2NPMNAMAMATA KULIAHKODE MATA KULIAHDOSENSEMESTERKELASPA
32011 1 0217 008aHukum Perindustrian
final_sheet
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,050
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Just filled out some other columns (The ones I could see you need). Instructions the same as in the last post (see quote)
Make sure the sheet name is exactly correct and most importantly you enter the formula without the curly brackets and then commit the formula with Ctrl Shift + Enter and not just Enter. If done correctly Excel will add the curly brackets. Do not try entering them manually.

Excel Workbook
ABCDEFGH
1BB100508
2NPMNAMAMATA KULIAHKODE MATA KULIAHDOSENSEMESTERKELASPA
32011 1 0217 008aHukum PerindustrianxxxV0xxx
final_sheet
 

IM_Haries

New Member
Joined
Nov 15, 2015
Messages
7
Just filled out some other columns (The ones I could see you need). Instructions the same as in the last post (see quote)


final_sheet

*ABCDEFGH
1**BB100508*****
2NPMNAMAMATA KULIAHKODE MATA KULIAHDOSENSEMESTERKELASPA
32011 1 0217 008aHukum Perindustrian*xxxV0xxx

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
C3{=INDEX('db-sheet'!E:E,MATCH(1,(A3='db-sheet'!$B:$B)*(C1='db-sheet'!$D:$D),0))}
E3{=INDEX('db-sheet'!G:G,MATCH(1,(A3='db-sheet'!$B:$B)*(C1='db-sheet'!$D:$D),0))}
F3{=INDEX('db-sheet'!H:H,MATCH(1,(A3='db-sheet'!$B:$B)*(C1='db-sheet'!$D:$D),0))}
G3{=INDEX('db-sheet'!I:I,MATCH(1,(A3='db-sheet'!$B:$B)*(C1='db-sheet'!$D:$D),0))}
H3{=INDEX('db-sheet'!J:J,MATCH(1,(A3='db-sheet'!$B:$B)*(C1='db-sheet'!$D:$D),0))}

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

OH MY GOD...!!!

You Solving my case....

thank you MARK858, You are smart guy...

but, could you explain to me the detail why or how these formula work?

i can copy and paste it, but i don't know how it work,

and, what the mean "Do not try entering them manually?

so i must create the formula that could entering automatically?

THANK YOU VERY MUCH BTW....!!! :)
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,050
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
"Do not try entering them manually?

I mean the curly brackets
enter the formula without the curly brackets and then commit the formula with Ctrl Shift + Enter and not just Enter. If done correctly Excel will add the curly brackets.

{=INDEX('db-sheet'!E:E,MATCH(1,(A3='db-sheet'!$B:$B)*(C1='db-sheet'!$D:$D),0))}

broken down...
'db-sheet'!E:E
The column you want the result to come from.

it is an array formula and returns 0's and 1's (False and Trues). This says look for the 1 (True).

A3='db-sheet'!$B:$B
matches A3 with Column B of db-sheet
C1='db-sheet'!$D:$D
matches D3 with Column D of db-sheet

asks for the matches to be exact matches.

Basically
MATCH(1,(A3='db-sheet'!$B:$B)*(C1='db-sheet'!$D:$D),0)
evaluates to 2
Then Index finds and returns the value from that position in the column

INDEX('db-sheet'!E:E,2) = E2


i can copy and paste it....
i must create the formula that could entering automatically
Need to do something with C1 first. Is that cell always static?
 
Last edited:

IM_Haries

New Member
Joined
Nov 15, 2015
Messages
7
You are the smart guy Mark,, however you explain this detailed, i still not so understand,
but Thank you very much for your genious helpp, could i ask u some questions?

where you learn the excel formula? just google it or you have a book or course?

and, in these data, actually i must solving the data ( a thousand row maybe) could you make this more automatically? if yes i will give you the data more detailed...

and... i have a gift for you (not money) :)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,580
Messages
5,637,211
Members
416,961
Latest member
sigrid6940

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
Top