Hi all, been driven crazy all week trying to work a formula out and could use some help. Basically I'm looking to get the equivalent of a VLOOKUP that returns all occurrences of a given single criteria from a given range. My problem is that I need to be able to use a wildcard, so doing INDEX alone is no good and I haven't been able to work something out using MATCH. However, I was playing around in Google Sheets and I was able to get EXACTLY what I needed, but naturally it wasn't able to properly convert when saving the sheet as an Excel file.
What I'm trying to put together is list of addresses and related data on one sheet, and then to be able to search for the street address on another sheet. Each address is going to be listed three or four times with different related data, and I need to be able to have results come up as I search for partials, because street addresses can naturally be entered incorrectly, misspellings, alternate versions, etc. I included the LOWER fuction as a kind of workaround because I need the search to be case insensitive.
Here is the link to the sheet and the QUERY
Google Sheet - The QUERY lives in cell cell B9. If you type a partial address in D6 and press enter, it searches the range in Data! and returns the results.
Thanks in advance for any suggestions!
Bookmarks