Using Filter and VSTACK on a Search Box with Multiple Conditions?

[Max Faraday]

Hello, I'm new to Excel. I tried a google search and I don't think I'm asking the right questions. Google did lead me here where I did a forum search. However, the one post that showed up wasn't really relevant to my situation. I have a workbook with 9 worksheets. The first worksheet is just a search bar with a table and header (First Name, Last Name, Position, Supervisor, and Email) under it to access information faster instead of looking through the worksheets themselves for the information. The code I have works great. The person inputs the first name and all the other relevant information about them pops up. However, I want it where if you don't know the first name you can search the last name or the position they work and have the same information pop up. Cells A2:A1 are cells containing first names. Here is the working code I have so far:

=FILTER(VSTACK('Sheet2 :Sheet8'!A2:F21),(VSTACK('Sheet2 :Sheet8'!A2:A21)<>"")*(VSTACK('Sheet2 :Sheet8'!A2:A21)=F2),"No Record Found")

I tried adding a comma after the A2:A21 cells and adding B2:B21:

=FILTER(VSTACK('Sheet2 :Sheet8'!A2:F21,B2:F21),(VSTACK('Sheet2 :Sheet8'!A2:A21,B2:B21)<>"")*(VSTACK('Sheet2 :Sheet8'!A2:A21,B2:B21)=F2),"No Record Found")

However, it returns an error

I also tried just doing the same code but adding a * for a 2nd condition but that didn't seem to work either:

=FILTER(VSTACK('Sheet2 :Sheet8'!A2:F21),(VSTACK('Sheet2 :Sheet8'!A2:A21)<>"")*(VSTACK('Sheet2 :Sheet8'!A2:A21)*(VSTACK('Sheet2 :Sheet8'!B2:B21)=F2),"No Record Found")

Any help would be appreciated.

 

[Herakles]

Hello, I'm new to Excel. I tried a google search and I don't think I'm asking the right questions. Google did lead me here where I did a forum search. However, the one post that showed up wasn't really relevant to my situation. I have a workbook with 9 worksheets. The first worksheet is just a search bar with a table and header (First Name, Last Name, Position, Supervisor, and Email) under it to access information faster instead of looking through the worksheets themselves for the information. The code I have works great. The person inputs the first name and all the other relevant information about them pops up. However, I want it where if you don't know the first name you can search the last name or the position they work and have the same information pop up. Cells A2:A1 are cells containing first names. Here is the working code I have so far:

=FILTER(VSTACK('Sheet2 :Sheet8'!A2:F21),(VSTACK('Sheet2 :Sheet8'!A2:A21)<>"")*(VSTACK('Sheet2 :Sheet8'!A2:A21)=F2),"No Record Found")

I tried adding a comma after the A2:A21 cells and adding B2:B21:

=FILTER(VSTACK('Sheet2 :Sheet8'!A2:F21,B2:F21),(VSTACK('Sheet2 :Sheet8'!A2:A21,B2:B21)<>"")*(VSTACK('Sheet2 :Sheet8'!A2:A21,B2:B21)=F2),"No Record Found")

However, it returns an error

I also tried just doing the same code but adding a * for a 2nd condition but that didn't seem to work either:

=FILTER(VSTACK('Sheet2 :Sheet8'!A2:F21),(VSTACK('Sheet2 :Sheet8'!A2:A21)<>"")*(VSTACK('Sheet2 :Sheet8'!A2:A21)*(VSTACK('Sheet2 :Sheet8'!B2:B21)=F2),"No Record Found")

Any help would be appreciated.

Welcome to MrExcel. If the answer is not on here then the question is wrong!!!

Are the 8 worksheets all of the same structure?

Can you use XL2BB to post some rows from one of the sheets including the first row. See last two paragraphs.

Is there a reason that the data is spread over 8 sheets?

It could be amangamated onto one sheet for searching purposes.

When you have identified the right person and the sheet upon where their details exist what do you want to happen?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Max Faraday]

Thanks for the welcome!
Each sheet is a different department. If for some reason I want to look at the whole department and who works under it I know exactly what sheet. All 8 worksheets have a table that is placed in the same area of the worksheets. Some worksheets have slightly smaller or bigger tables that's why I put it as 21 vertical cells. I suppose I could put them all on one worksheet if I needed to. Once I have identified the right person I want it to show up on the first sheets table that has exactly the same headers. So If I need to enroll someone specific into a course I have all their info pop up on the table so I don't have to go through the worksheets to find it. I can't really do the same copy of data as the information is sensitive.

 

[Herakles]

Thanks for the welcome!
Each sheet is a different department. If for some reason I want to look at the whole department and who works under it I know exactly what sheet. All 8 worksheets have a table that is placed in the same area of the worksheets. Some worksheets have slightly smaller or bigger tables that's why I put it as 21 vertical cells. I suppose I could put them all on one worksheet if I needed to. Once I have identified the right person I want it to show up on the first sheets table that has exactly the same headers. So If I need to enroll someone specific into a course I have all their info pop up on the table so I don't have to go through the worksheets to find it. I can't really do the same copy of data as the information is sensitive.

I suggest one table with the first column being the department name then you can filter by department to look the same as if you are looking at a seperate sheet for that
department.

You can also filter the table using the search criteria.

If you ever need seperate worksheets then it would not be difficult to create them as and when needed.

 

[Fluff]

How about

=LET(d,VSTACK(Sheet2 :Sheet8!A2:F21),FILTER(d,BYROW(d,LAMBDA(br,ISNUMBER(XMATCH(F2,br)))),"No Record Found"))