excel and access

[John_BloggsJohn_blk1405]

Good day, I have a large amount of data (for example, I put 1000 lines of data, my normal file is 70 mb in size)I search the data in cells B9, C9 , D9 , E9 , F9 in Sheetst1 in the data sheet and call the same data from the cells A11 , B11 , C11 ,D11 , E11 , F11 in the datasu sheet but my file normally crashes because it's too big.I want to do this through ACCESS but I don't know how to do it. How can I load my data into ACCES application and call it from there to my sheets1? I have attached the sample file

[#2 · Accepted solution]

@John_BloggsJohn_blk1405 

=IFERROR(INDEX(datasu!$E$2:$E$1000,SMALL(IF((datasu!$F$2:$F$1000=SHEETST1!$F$2)*(datasu!$G$2:$G$1000=SHEETST1!$G$2)*(datasu!$H$2:$H$1000=SHEETST1!$H$2)*(datasu!$I$2:$I$1000=SHEETST1!$I$2)*(datasu!$J$2:$J$1000=SHEETST1!$J$2),ROW(datasu!$E$2:$E$1000)-1),ROW($A1))),"")

An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021. The formula is in cell H11 and filled down in the attached file.

=IFERROR(VLOOKUP($H11,datasu!$E$2:$J$1000,COLUMN(B$1),FALSE),"")

This formula is in cell I11 and filled across range I11:M19.

For the dropdown reference i've created lists of unique items in columns M to Q.

Which version of Excel do you work with and how many rows are there in your actual sheet? The formula should work well with thousands of rows.

[#3]

@John_BloggsJohn_blk1405 

You can create a table in a new Access database with the same fields as the table in the datasu sheet, and import or copy/paste the data.

See the attached sample database.

I imported the data and built a simple filter form.

[#4]

my friend thank you very much for your help. is it possible to do this with acces, so I want to make an access connection with excel.I want my data that is in Access to be fetched according to the data on my sheetst1 page. @OliverScheurich 

i'm sharing a link because access gave an error when uploading my file.I am also sharing the virus results of my file

https://dosya.co/os7l54d45ikh/Database1.accdb.html

VirusTotal - File - 35fe0702f36005f870976d6cae2000caaa1c6cdcc725ec5b4528eea93bb0a1ab

[#5]

@John_BloggsJohn_blk1405 

You are welcome. I don't have Access because i work with Office 2013 Home and Student. Therefore i can't help making an Access connection with Excel unfortunately.

[#6]

as access is a relationship database，you can query data by sql like below。

select * from Sayfa1 where OBJECTID=1 and TANIM like 'main artery' and SERIT='2t' and CINS='asphalt' and SHAPE_Length=177;