User Profile
ChazS
Copper Contributor
Joined 3 years ago
User Widgets
Recent Discussions
Retrieving Full Rows For Any Cells Matching Search Query
Hi! I use Spreadsheets for pretty basic stuff but have been trying to get into more complex uses for my new job. Something I'm setting up is a database for customer calls we receive, cataloguing their names, numbers, emails, complaints, etc., and I'd like to implement a search function where information entered into a specified cell will pull full rows that contain the query in any of its cells. I formulated the following into a test document after doing some reading online (noteI haven't applied ISERROR yet because I'm taking this formula step by step in order to understand it as fully as possible.): =INDEX($A$2:$D$10,SMALL(IF($A$2:$D$10=$B$12,ROW($A$2:$A$10)),ROW(1:1))-1,0) The thing is, for various reasons I've been using open source programs such as LibreOffice at home and am re-orienting toward Excel since my job uses it extensively. When plugged into LibreOffice (I selected cells A15:D15, applied formula with Ctrl-Shift-Enter, Ctrl-dragged down), the above formula gave me exactly what I want: a query in cell B12 brings up the full rows of information for any cells containing the query. When I brought this over to Excel, the same formula only pulls the first column of data and repeats it across the row. Is there a way to modify this formula to mirror the results I got in LibreOffice, or is there another method I must implement to get the same results? Excel version: Microsoft® Excel® for Microsoft 365 MSO (Version 2111 Build 16.0.14701.20240) 32-bit PC: Windows 10 Pro I would attach the test file as suggested but I can't find an explanation of how to do that on this site. Much appreciated!
