Copy the cell values of a column whose certain rows are hidden, to another sheet

[Ian Y]

Here are two demonstrative sheets:

Demo1.xlsx
	A	B
1	ID	Text
2	zQmGwsBXVa	Lorem ipsum dolor sit amet. Consectetur adipiscing elit.
3	8tcbmfv2dl	Aliquam pulvinar tincidunt lorem vel cursus.
4	U7km0cE1UV
5	Lm3BK6UrpS	Pellentesque tincidunt purus nec. Enim molestie venenatis. Nunc tempor dapibus dui, sed tempor mi fermentum pretium.
6	IOwCYwdCzP
January 7

Demo2.xlsx
	A	B
1	ID	Text
2	rUHBLBZZyc
3	zQmGwsBXVa
4	LxNNV0uEwx
5	oiNo2eOZIU
6	JV8n6HfJAE
7	8tcbmfv2dl
8	q0hZR5UrGi
9	CDFzs37XMd
10	Lm3BK6UrpS
11	JZxjBnip0M
January 8

In Demo1.xlsx, by using filter to hide row whose cells in column B are empty, I can copy other row's Text values all at once easily:

My question is, how do I fill in those copied Text values to the cells whose corresponding IDs are zQmGwsBXVa, 8tcbmfv2dl, and Lm3BK6UrpS, respectively in column B in Demo2.xlsx all at once easily? The order of rows is not to be worried about. In Demo2.xlsx, zQmGwsBXVa always appears before 8tcbmfv2dl, which always appears before Lm3BK6UrpS.

The two sheets are streamlined versions for demonstrative purposes. The actual sheets can contain more than 1,000 rows.

 

[Ian Y]

After having success in testing the demonstrative sheets, I started to work on my actual sheets. However, after applying the same formula into my actual sheets, cells which are supposed to be empty contain 0 unwantedly. I don't know what caused the issue. I'm using the same software (Microsoft Excel 2019) on my same PC.

Anyway, I resorted to using the IF function and the formula became something like:

=IF(IFNA(VLOOKUP(A2,'D:\Administrator\Desktop\[Demo1.xlsx]January 7'!$A:$B,2,0),"")=0, "", IFNA(VLOOKUP(A2,'D:\Administrator\Desktop\[Demo1.xlsx]January 7'!$A:$B,2,0),""))

That worked and the 0s are gone, but the new formula looks a bit long-winded.

What could be the culprit of the issue? A is it possible to shorten the the new formula?

 

[DanteAmor]

A is it possible to shorten the the new formula?

Note: The following formula works only when the result returned by the formula is text.


=IFNA(VLOOKUP(A2,'D:\Administrator\Desktop\[Demo1.xlsx]January 7'!$A:$B,2,0),"")&""