I’m working with a Google Sheets document that contains survey data collected from Google Forms. I need to pull out particular information from this dataset and display it on a summary sheet.
My goal is to find and return a person’s name when several conditions are satisfied simultaneously. For instance, I want to get the employee name when their record matches specific criteria like department, work schedule, location, and other factors all at once.
I attempted to use the FILTER function but it returns entire rows instead of just the single cell I need. I also experimented with VLOOKUP but it only allows me to match against one condition, while my situation requires checking multiple criteria before extracting the target value.
What formula or approach would work best for this scenario? I need something that can evaluate several conditions in the same row and then return just one specific cell value when all conditions are true.
INDEX/MATCH with multiple criteria is exactly what you need. I’ve used this for years with survey data - works perfectly. Just concatenate your criteria columns and search values, then use INDEX to grab the specific cell. Formula looks like: =INDEX(name_column, MATCH(criteria1&criteria2&criteria3, column1&column2&column3, 0)). For your situation: =INDEX(B:B, MATCH(F2&G2&H2, C:C&D:D&E:E, 0)) where B:B has names, F2:H2 are your search criteria, and C:E are the data columns. This handles multiple conditions efficiently and gives you just the single value instead of entire rows. Enter it as an array formula in older Google Sheets - newer versions do this automatically.
QUERY’s another solid option that handles multiple conditions naturally. I’ve dealt with similar survey data extraction and found QUERY way more readable than complex INDEX/MATCH formulas. Try =QUERY(A:F, "SELECT B WHERE C='Department' AND D='Full-time' AND E='Remote'") where B is your name column and C, D, E are your criteria columns. Just swap the quoted values for your actual criteria or cell references. It follows SQL-like structure so it’s easier to modify when you need to add or change conditions. Plus you can easily switch between single values or multiple matches by tweaking the SELECT clause. Works great with text and numbers - just wrap text values in single quotes within the query string.
try FILTER with INDEX - it wraps around the filter function so u get just the cell instead of the whole row. Something like =INDEX(FILTER(name_range, (criteria1_range=value1)*(criteria2_range=value2)*(criteria3_range=value3)), 1) - the multiplication works like AND for multiple conditions. works great for pulling survey data.