I want a column that automatically fills in with the person's name when an ID number is added. There is another tab that provides the information
| Date | ID | Member_Name |
|---|---|---|
| 2/1/21 | 3 | Michelle (AUTOMATICALLY FILLS IN) |
=(query('Team / Partner'!A:B,"Select B where A Matches '"&B2&"'",0))
The query above works when I drag the formula down. That is when I drag it into row 3 the reference cell changes to B3 as opposed to staying B2.
However, when I put it in the array formula it doesn't automatically update.
=ArrayFormula(IF(LEN(B:B),if(ROW(B:B)=1,"Member_Name",(query('Team / Partner'!A:B,"Select B where A Matches '"&B2:B&"'",0))),))
The reference table looks like this:
| ID | Member_Name |
|---|---|
| 3 | Michelle |
I've also tried to remove the number from the reference cell:
=ArrayFormula(IF(LEN(B:B),if(ROW(B:B)=1,"Member_Name",(query('Team / Partner'!A:B,"Select B where A Matches '"&B:B&"'",0))),))
I tried vlookup but it doesn't work either. I believe this is because the reference table uses an array formula to autofill in the member id numbers. below is the formula i'm using:
=ArrayFormula(IF(LEN(B:B),if(ROW(B:B)=1,"Member_ID",FIXED(ROW(A:A)-1,0)),))
I also tried an index + match version. But "B2" doesn't automatically change to B3 like id like it to.
=ARRAYFORMULA(if(B2:B="","",(INDEX('Team / Partner'!B:B,(B2+1)))))
I want B2 to be a relative reference, not an absolute reference. Any ideas?