I Just cant mange this - Help
Im trying to do something but just cant manage it.
If you look at the image below you will see Username, Sponsor, Referral 1, Referral 2.
IMAGE of spreadsheet - https://i.sstatic.net/rRP1B.jpg
Now what i want to do with a formula is E&F or how ever i can do it to search Sponsor for using the corresponding usersname and if there is none i want to place No referrals in the referral 1 & referral 2. There will only ever be 2 referrals. Ive spent hours doing it but cant get it right.
I've tried Arrayformula, lookup and loads of other ways but new to spreadsheets and cant master this
In F6:
=ArrayFormula(IF(LEN(D6:D),IFERROR(VLOOKUP(D6:D,{E6:E,D6:D},2,0),"No Referrals"),))
and in G6:
=ArrayFormula(IF(LEN(D6:D),IFERROR(VLOOKUP(D6:D,FILTER({E6:E,D6:D},(MATCH(E6:E,E6:E,0)+ROW(E6)-1)<ROW(E6:E)),2,0),"No Referrals"),))