Reversing a Formula to specify where data is placed?

[hazmat]

Not sure if my thread title is correct, but this is what i would like to do, and have no idea where to even start.

I have 2 sheets in same workbook (pic below)
I have the following formula in Sheet 1, Column A to create a new list, from Sheet 2, Column A, that puts the blanks cells at bottom as shown.

{=IFERROR(INDEX(Sheet2!$A$2:$A$31,SMALL(IF(Sheet2!$A$2:$A$31<>"",ROW(Sheet2!$A$2:$A$31)-ROW(Sheet2!$A$2)+1),ROWS(Sheet2!$A$2:$A2))),"")}

If a date is entered in Sheet 1 Column B, next to a Name (or Names), I need that Date placed in the proper cell In Sheet 2 Column B.
So, for example, If the user enters a date next to Name9 in Sheet 1, (Cell B4), that date will automatically be placed next to Name9 in Sheet 2, (Cell B10)
Thanks for any and all help

 

[Scott Huish]

What version of Excel are you using?

If 365, I would use XLOOKUP, older versions VLOOKUP

 

[Snakehips]

@hazmat assuming not using 365, try like below

Book1
	A	B
1	Member	Date
2
3
4
5
6	Name5	1/1/24
7	Name6
8
9
10	Name9	1/3/24
11
12
13	Name2
14
15
16
17
18	Name7	1/5/24
19
20
21
22	Name1
23
24	Name3	1/7/24
25
26
27
28
29	Name8	1/8/24
30
31
32
33
34
35
Sheet2

Cell Formulas
Range	Formula
B2:B33	B2	=IFERROR(IF(VLOOKUP(Sheet2!A2,Sheet1!A:B,2,FALSE)>1,VLOOKUP(Sheet2!A2,Sheet1!A:B,2,FALSE),""),"")
B34:B35	B34	=IFERROR(INDEX(Sheet1!B:B,MATCH(Sheet2!A34,Sheet1!A:A,0)),"")

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[hazmat]

@Snakehips
Thanks. That gets me half way there.
I should have stated that when a date is entered in Sheet 1 Column B, the list in Column A will be reorganized.
Since my original question is only a piece of the overall puzzle, I am going to apply your formula to my project and see where I stand, and if i can figure out what I ultimately need to happen. Most likely I will be coming back here with more questions.

Also, What is the formula in cells B34:B35 doing? It doesn't seem to make any difference whether its there or not.

@Fluff Version and Platform updated

 

[Snakehips]

Thanks. That gets me half way there.
I should have stated that when a date is entered in Sheet 1 Column B, the list in Column A will be reorganized.
Since my original question is only a piece of the overall puzzle, I am going to apply your formula to my project and see where I stand, and if i can figure out what I ultimately need to happen. Most likely I will be coming back here with more questions.

Also, What is the formula in cells B34:B35 doing? It doesn't seem to make any difference whether its there or not.

Unrelated formulas copied in error. Just delete