| Name | ID |
|---|---|
| John | 2 |
| Smith | 3 |
| Tom | 1 |
| Order_ID | Customer_ID | User_Name |
|---|---|---|
| 3003 | 1 | =filter(Name,u_id = B2) |
| 3004 | 3 | =filter(Name,u_id = B3) |
| 3005 | 2 | =filter(Name,u_id = B4) |
I have the two tables above. The first one is a user list and the 2nd one order list. I have two named range in the first table: Name and u_id.
What I am trying to do is to get users' names from table1 and put them to the order table.
It is not very hard to use filter function for this. But it has a shortcoming: Since ew user data and order data is coming in every day, I have to manually drag the formula to fill the whole User_Name column even for the rows without data. This will generate #REF error, and I will have to refine my formula to
=IF(ISBLANK(B2),"",filter(Name,u_id = B2))
In my real project, I have to use a lot of IF..ISBLANK() to a point the formula is almost unreadable. Also, having to drag the formula from time to time ( Yes I know I can fill 1000 row at once ) doesn't feel very good.
So my question is, is there a better practice for this task? For example, some array function to put into Table2's C2 and I don't have to worry anything else?
CodePudding user response：
Use arrayformula at the top of the column with iferror condition, and vlookup instead of filter
Try in C1
=ARRAYFORMULA(IFERROR(VLOOKUP(order_uid,{user_id,user_name},2,0)))
