I’m having trouble creating a formula in Google Sheets and could really use some help.
I have a spreadsheet with columns for Employee, Manager, Contact 1, and Contact 2. What I’m trying to do is create a formula that will look up the Manager column using the Employee name as the search key. If no matches are found, I want it to display “No contacts found” in both the Contact 1 and Contact 2 columns.
The tricky part is that there will never be more than 2 contacts per employee, so I need the formula to handle this limit properly. I’ve been working on this for quite a while now and have tried different approaches including VLOOKUP, INDEX/MATCH, and array formulas, but I’m still pretty new to spreadsheets so I can’t seem to get the syntax right.
Can anyone help me figure out the correct formula structure for this kind of lookup operation? I feel like I’m missing something obvious but just can’t put my finger on it.
sounds like your data structure might be the issue here. If you’ve got separate rows for each manager-employee combo, ditch VLOOKUP and use FILTER instead - try =FILTER(B:B,A:A=E2) where E2 is your employee name. This grabs all matching contacts automatically. Then use INDEX to pull the first and second results for your contact columns. Much easier than forcing VLOOKUP to handle multiple matches.
Had this exact problem last month with our project tracking sheet. You’re probably overcomplicating the lookup part. Since your Manager column has the contacts you need, just use a simple VLOOKUP. For Contact 1, try =IFERROR(VLOOKUP(A2,$A:$B,2,FALSE),"No contacts found") where A2 is your Employee name and column B has your Manager data. For Contact 2, I need more info about your setup. Are you storing multiple contacts in separate rows for each employee, or in different columns? The formula changes based on how your data’s organized. Can’t give you exact syntax without seeing the layout, but that IFERROR wrapper will handle your “No contacts found” part perfectly.
Depends on how your contacts are set up - are they in one cell with separators, or split across multiple rows? If they’re in separate rows with the same employee name, skip VLOOKUP and use QUERY instead. Try =QUERY(A:B,"SELECT B WHERE A='"&E2&"' LIMIT 2") to grab both contacts at once. Then use INDEX and ROW functions to split the results into your contact columns. QUERY’s way more flexible for this stuff, and you can wrap it with IFERROR to show “No contacts found” when there’s nothing to return.