1

I've tried everything and am finally turning to help from this community to figure out this Google Sheets formula. Here is a sample sheet to illustrate the situation: https://docs.google.com/spreadsheets/d/1mLzsAyqtkASYMQsu8-igaUTURvhHiH-jXJN3qsr0mkU/edit?usp=sharing

Each row is a patient that visited a clinic. There are 5 fields for patients to provide phone numbers (cell, home, etc.). On another tab ("Paid Calls") there are phone numbers in column A from leads that called us. As this will be an ever-growing list of patients and calls, I'd like to use an array formula to search across all five phone numbers provided by each patient, compare them to the phone numbers from Col A in Paid Leads and return the value of the first matching phone number. So, if the cell phone of patient 999999 matches any of the phone numbers in the Paid Leads tab, then I want to return that matching phone number.

Because I'm not sure there's a way to do this, I have an alternate scenario that could work where an array formula will return the first non-blank value from columns E through I of each row. If that's an easier formula to write, then I can figure out a way to arrange the data to make that work using a non-related query.

Thank you for any help you can provide!

Improve this question

2 Answers 2

Reset to default
1

There's two formulas you can use:

This one compares the phone number of a specific patient with the number list you have in the PaidCalls sheet.

=join(char(10),FILTER(E2:I2,ARRAYFORMULA(ISNUMBER(MATCH(E2:I2,PaidCalls!A2:A,0)))))

The other one simply iterates through each patient's phone numbers and selects the first one which isn't blank. It's pretty straight forward. It's a series of nested conditions, that all follow the following pattern:

If this cell is not blank, use its value. If it is blank, proceed to the next cell.

=IF(not(isblank(E2)),E2,IF(not(isblank(F2)), F2, IF(not(isblank(G2)), G2, IF(not(isblank(H2)),H2, IF(not(isblank(I2)),I2)))))

PS: Please note that I used the name 'PaidCalls' instead of 'Paid Calls' in my implementation. Everything else works according to the structure of your sheet. I also already copied it into the Google Sheet you shared.

Please let me know if you'd like any further explanation, particularly on the first formula.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thank you so much for taking the time to help. Couple questions: 1) even if there are two matching phone numbers in the row, I can only have one of the matching numbers returned because I’m going to use that new column as a vlookup for additional details about the patient. Can that formula you wrote using =join only display one of the matching values? (doesn’t matter which one) 2) can these formulas you provided be used with array formula parameters so we don’t have to manually drag down when new rows are added? I know there were some solutions I found that worked but not with arrays.
Dear Keith, you're most welcome. I'm just about to head to bed, but I'll look into that tomorrow or maybe the day after. Thanks for your patience. If you could upvote the answer in the meantime, that'd be appreciated though. :)
About to take off on a flight but will ASAP. Thanks again.
No further action needed on this, your formulas worked great. Thank you!
0

formula that will return the phone number from this row that matches any phone number from Column A in the Paid Calls tab.

=JOIN(CHAR(10),unique(QUERY(ArrayFormula(IFERROR(VLOOKUP(
 {E5;F5;G5;H5;I5},PaidCalls!A$2:A,1,0),)),
 "select Col1 where Col1 is not null", 0)))

formula that will return the first non-blank value from columns E through I of each row below

=INDEX(QUERY(transpose(E5:I5),"select Col1 where Col1 is not null",0),1,1)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.