Hi, I am trying to figure out a part of the array formula:
={“SenderName”;ArrayFormula(if(len(D2:D),VLOOKUP(C2:C,{‘Sheet name’!B2:B,‘Sheet Name’!E2:E},2,0),""))}
What does (if(len mean?
Thank you!
That’s just a way of checking if a column in the row is populate. If it is, then go ahead to run the formula. If not then it returns a blank. Otherwise it could put garbage in ever single row and glide would see it and count it against your total row count.
How it works, is if there is something in the D column, then the length is greater than zero and seen as true. If true, it does the vlookup. If false, it write “” (blank) to the cell.
3 Likes
Ahh gotcha! So if I wanted to make a Receipient Column is would be the opposite?
={“RecipientName”;ArrayFormula(if(len(C2:C),VLOOKUP(D2:D,{‘Sheet Name’!B2:B,‘Sheet Name’!E2:E},2,0),""))}
In these cases… Column C is the sender and Column D is the recipient business row IDs, and I am running this formula to populate the business name in two other columns by looking at the main directory Ex: ‘Sheet Name’.
1 Like
Yep, that should work. Whenever I’ve done it, I’ve checked against a column that always had a value in it, such as Row ID. Doesn’t really matter which column you use. It’s just telling it to run the formula on populated rows instead of empty rows.
1 Like
Perfect. Thank you! Lastly what does the 2,0 mean at the end?
I know you said the 0 is so it looks for data that is greater than 0 characters aka blank?
Those are the last two parameters of a VLOOKUP formula. Indexed and Sorted.
Index is which column in your searched range you want to return. Sorted is just a true or false value to indicate if the data you are searching is sorted or not. It affects what is returned, but if you are searching for an exact match value, then sorted can be false. In this case it’s using ‘0’ to indicate false.
2 Likes
Thanks! I will review this.
1 Like