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!
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.