Fancy lookup formula!

[ryansm05]

Hi,

I need a formula that can look down entirety of Column B and identify all the blank cells that have a corresponding Ref number in column A.

The answer I would be looking for is in column D. Apologies for not attaching any files (company restriction prevents me form doing so).

If you can help it would be much appreciated.

Thanks,
Ryan.


[TABLE="width: 203"]
<colgroup><col width="64" style="width: 48pt;" span="2"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2816;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 64, bgcolor: #CCCCFF"]Ref (A)
[/TD]
[TD="width: 64, bgcolor: #CCCCFF"]Employee (B)
[/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Answer (D)
[/TD]
[/TR]
[TR]
[TD="bgcolor: #CCCCFF"]#54482[/TD]
[TD="bgcolor: white"]1611400158[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #CCCCFF"]#55066[/TD]
[/TR]
[TR]
[TD="bgcolor: #CCCCFF"]#54490[/TD]
[TD="bgcolor: white"]1613033575[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #CCCCFF"]#55070[/TD]
[/TR]
[TR]
[TD="bgcolor: #CCCCFF"]#54752[/TD]
[TD="bgcolor: white"]1613046654[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #CCCCFF"]#55072[/TD]
[/TR]
[TR]
[TD="bgcolor: #CCCCFF"]#54844[/TD]
[TD="bgcolor: white"]1613075302[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #CCCCFF"]#55075[/TD]
[/TR]
[TR]
[TD="bgcolor: #CCCCFF"]#55066[/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #CCCCFF"]#55076[/TD]
[/TR]
[TR]
[TD="bgcolor: #CCCCFF"]#55067[/TD]
[TD="bgcolor: white"]1608229877[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #CCCCFF"]#55083[/TD]
[/TR]
[TR]
[TD="bgcolor: #CCCCFF"]#55070[/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #CCCCFF"]#55103[/TD]
[/TR]
[TR]
[TD="bgcolor: #CCCCFF"]#55072[/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #CCCCFF"]#55075[/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #CCCCFF"]#55076[/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #CCCCFF"]#55083[/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #CCCCFF"]#55103[/TD]
[TD="bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Try this, copied down.

00 Old HTML Conversions 2020-04-08 1.xlsm
	A	B	C	D
1	Ref (A)	Employee (B)		Answer (D)
2	#54482	1611400158		#55066
3	#54490	1613033575		#55070
4	#54752	1613046654		#55072
5	#54844	1613075302		#55075
6	#55066			#55076
7	#55067	1608229877		#55083
8	#55070			#55103
9	#55072
10	#55075
11	#55076
12	#55083
13	#55103
List

Cell Formulas
Range	Formula
D2:D11	D2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$13)/((B$2:B$13="")*(A$2:A$13<>"")),ROWS(D$2:D2))),"")

 

[Fluff]

How about
=IFERROR(INDEX($A$2:$A$13,AGGREGATE(15,6,(ROW($A$2:$A$13)-ROW($A$2)+1)/($B$2:$B$13=""), ROWS(D$2:D2))),"")

 

[Misca]

Try:

=IF(ROW()-1>ROWS($B$1:$B$13)-COUNTA($B$1:$B$13),"",INDEX($A$1:$A$13,SMALL(IF(LEN($A$1:$A$13&$B$1:$B$13)=LEN($A$1:$A$13),ROW($B$1:$B$13),ROWS($B$1:$B$13)+1),ROW()-1)))

Enter with Ctrl + Shift + Enter

 

[ryansm05]

Thank you all! It worked a treat so I'm super chuffed with that. Have a great weekend.

Ryan

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Peter_SSs]

Thank you all! It worked a treat so I'm super chuffed with that. Have a great weekend.

Ryan

You're welcome.


BTW, if your data is as uniform as your sample, here is an even shorter formula to do the job.

00 Old HTML Conversions 2020-04-08 1.xlsm
	A	B	C	D
1	Ref (A)	Employee (B)		Answer (D)
2	#54482	1611400158		#55066
3	#54490	1613033575		#55070
4	#54752	1613046654		#55072
5	#54844	1613075302		#55075
6	#55066			#55076
7	#55067	1608229877		#55083
8	#55070			#55103
9	#55072
10	#55075
11	#55076
12	#55083
13	#55103
List

Cell Formulas
Range	Formula
D2:D11	D2	=IFERROR("#"&AGGREGATE(15,6,RIGHT(A$2:A$13,5)/(B$2:B$13=""),ROWS(D$2:D2)),"")