Forum Discussion
Solved
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...
=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.
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.