•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to have a multi-criteria lookup find the most specific result when multiple results are possible?
Our take
Struggling with multi-criteria lookups that yield multiple results? You're not alone. In Google Sheets, finding the most specific outcome from a dataset can be tricky, especially when dealing with overlapping data points. By leveraging advanced functions and strategic criteria, you can ensure that your lookups pull the most relevant results, even when "All" is involved. This guide will walk you through creative solutions to refine your search and maximize accuracy, ensuring your data retrieval process is both efficient and effective. Let’s dive in!
I've never used google docs before to share, so let me know if that link doesn't work.
I created a test doc similar to the real doc I'm working on. Essentially I have data that utilizes multiple columns of information to create a unique scenario. I need it to look up the result from a seperate sheet based on the most specific data it can and give me the result.
For example on my test sheet:
State - Ohio, City - Cincinnati, County - Hamilton, Township - Delhi, School - Oak Hills should pull results H from tab A and 8 from tab B
The issue I run into is that from tab A, the results H, I and J could all fit for the data point in the example above. So figuring out a way to only produce the most specific result (specific being the furthest column to the right) as well as account for any data points that include "All" instead of something specific.
Any creative solutions for this?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Need to return a value based on two different search criteria, multiple lines, VLOOKUPHi All, I have a report that I run at work that doesn't come out in a way that makes it easy to work with in an excel spread sheet. The best i can get it is a txt file that when pasted spreads values into different cells. I use VLOOKUP in other sheets to find one value and return another value in the same row. I don't know how to (or if it's even possible) to do this with multiple values on different rows. Could really use some help. I need some sort of VLOOKUP/XLOOKUP type of formula that does the following: -Searches Column A for a specific value -Then Searches Column B (Below where Column A value was found) for another value -Returns the value in Column C (Same Row as B) based on the value from B I'm pretty much stuck using Excel for this as my company doesn't allow outside/other programs to further organize the data. Macros may also be an option if anyone knows any. Thanks in advance with any answers. submitted by /u/QuandoOmniFluncas [link] [comments]
- Xlookup from another page in Google SheetsHello, I'm a novice user and trying to make a spreadsheet to help with work, using multiple conditions from an order to generate a list of needed parts. I'm getting an error when attempting to reference data from a drop down on another page. I've attached a trimmed down example that I can share without posting work info. https://docs.google.com/spreadsheets/d/1UT6k7b7Sqi0mV6L867CS_jSVQXN15ZyDkHa3gPlTgrg/edit?usp=sharing The real document has more of basically variable, but I think part of the complexity problem is the part numbers I'm trying to generate use the inputs in different ways and in different orders for each part, so I'm trying to make a page for each kind of part data and keep a cleaner UI page. There's probably a cleaner way to do this, that may or may not work with all the actual inputs I need to do, but I'll take any and all advice, I'm just a novice at best. Thank you. submitted by /u/Zestyclose_Silver352 [link] [comments]
- Searching for mulitple criterea in an unstructered cellHello people, english isn't my first language so I apologize in advance if my description of the issue isn't the best or I don't use the proper terminology. Problem Here is what I'm hoping to achieve. I'm trying to match entries based on partial contents of the cells with mulitple criterea. For example in the picture in column F ' Text'. There are mulitple different things contained in it, in no set order. But every entry in column F contains either the reference in column A oder the casenumber in column B, or sometimes both. For the data in columns A, B and C I can say that the row is always matched, so a row always belongs together. I have been trying to give out the name in column C into column F with VLookup, so that Smith would show up in F2, Park F3, Smith F4, Smith F5, and Miller F6 I have been unsuccesful so far trying to use partial matches or filter for character lenght, but nothing I could find seems to be suitable for this situation. I have googled a lot and watched a few dozen videos but didn't find a solution, but maybe I have not been looking for the right things. I hope someone can help me with a formula or method this can be achieved. Thanks in advance! submitted by /u/LackingExperience55 [link] [comments]
- Complex data sort needed across 3 columnsI have a spreadsheet where I want to return a subset of rows. There are 3 relevant columns: A, B, and I. They are all text values. I want to return all columns where column A="B" ("B" is a person) Of that subset, I want to see all rows where column I does NOT equal "P" ("P" is a location) Of that subset, I want to see all rows where column B's text value "blah" (a task) doesn't also exist as the column B value in the subset of rows where A="B" and I DOES equal "P". In other words, me, a person, am going to location P today, and I want to find out if there is a task that I can do there that hasn't been done there before, but HAS been done at a different location. Thanks! submitted by /u/sowtime444 [link] [comments]