excel and access

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...

DATA.xlsb133 KB

excel

Formulas and Functions

Macros and VBA

OliverScheurich
Dec 17, 2022
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.
DATA.xlsb135 KB

John_BloggsJohn_blk1405

Copper Contributor

Dec 17, 2022

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

https://www.virustotal.com/gui/file/35fe0702f36005f870976d6cae2000caaa1c6cdcc725ec5b4528eea93bb0a1ab?nocache=1

DATA.xlsb19 KB