help with formula & functions (no Macros & VBA)

[addori]

Hey all,

I've created a sheet that has the following columns: (A) dates, (B) names, (C) phone numbers, (D) free text & (E) Status (done or closed).

The first row (frozen) contains the title of each column.

I've opened another sheet (same file) and called it 'Dashboard" . I am looking to create some sort of index where I can type in (search really) for a phone number and it will return the entire rows back with all the information. Same goes to searching for a name and return the entire relevant rows (with the phone number, dates and etc). 

I've been trying to look for an answer online, and also tried by myself for 2 months .. and for the life of me, came back nothing but frustration. 

Important to mention that I can't use macros & VBA as my workplace won't allow it due to policy issues. Needless to say, the information is sensitive. 

Any ideas on how to do it and tackle it in the best way possible? 

Big thank you in advance!

[#2]

@addori 

=FILTER('same file'!A2:E14,

IF(I2<>"",'same file'!C2:C14=Dashboard!I2,

IF(J2<>"",'same file'!B2:B14=Dashboard!J2)))

Maybe with FILTER function if you work with Office365 or 2021. If there is search criteria in cell I2 the data is filtered for a matching phone number otherwise for a matching name according to criteria in cell J2.

[#3]

@addori  attached is a sheet with an example of how to do a few things:

a) create data drop downs that auto filter based on partial entries (I.e. type "jo" to filter the dropdown to only show "john", "joseph", "joanne", etc..

b) to use then use a set of fields to return rows of a table that meet those inputs

The data in on 1 sheet and the lookup is on a second sheet with the 'helper' columns used to create the dynamic drop downs over on the right around columns M,N,O, etc...  (you could hide these)

[#4]

Hey @mtarler. Thank you for the reply (sorry It took me a while to get back).
I tried to use your file as a sample, but one I open it and try to use the method you offered I get an error (invalid). So I know for fact I'm doing something wrong.
Also, I'm using two sheets in the same workbook. So in one there's my data and the other I use it as an "index".

[#5]

what version of Excel are you using? In particular are you using Excel 365? The workbook I sent uses FILTER() function which is only available in Excel 365.
If you ARE using Excel 365 then please explain more what isn't working or what you see when you open my sample.