lookup with vba in private sub

[thespardian]

Hi there!
@Fluff please.
Sheet 2 may be called Database. I entered the data over here and the Sheet3 may be called result sheet where I have entered the following formulas in column A and column B respectively.

Formula is Column A (on Sheet3)
=IF('SH3'!$C3="","",INDEX('SH2'!$C$3:$C$10000,MATCH('SH3'!$C3,'SH2'!$A$3:$A$10000,0)))

Formula in Column B (on Sheet3)
=IF('SH3'!$C3="","",INDEX('SH2'!$D$3:$D$10000,MATCH('SH3'!$C3,'SH2'!$A$3:$A$10000,0)))

There is a lot of data in Sheet2, The above formula is making it slow. Therefore I want to replace these formulas with a vb code that works with selection change event. Any help will be highly appreciated.

 

[bebo021999]

Try to upload in XL2BB mode to get quick solution!

 

[thespardian]

Thanks a lot for the guidance @bebo021999

This the database sheet (Sheet2)

book4.xlsm
	A	B	C	D
2	Ref	Date	Project#	Head
3	398 25.08.20	09.11.2021	7620	p
4	399 25.08.20	10.11.2021	7620	c
5	421 08.09.20	11.11.2021	7620	s
6	422 08.09.20	12.11.2021	7620	es
7	423 08.09.20	13.11.2021	7620	l
8	400 26.08.20	09.11.2021	7620	st
SH2

And this is the result (Sheet3)

book4.xlsm
	A	B	C
2	Project#	cHead	Ref
3	7620	p	398 25.08.20
4	7620	c	399 25.08.20
5	7620	s	421 08.09.20
6	7620	es	422 08.09.20
7	7620	l	423 08.09.20
8	7620	st	400 26.08.20
9
SH3

Cell Formulas
Range	Formula
A3:A9	A3	=IF('SH3'!$C3="","",INDEX('SH2'!$C$3:$C$10000,MATCH('SH3'!$C3,'SH2'!$A$3:$A$10000,0)))
B3:B9	B3	=IF('SH3'!$C3="","",INDEX('SH2'!$D$3:$D$10000,MATCH('SH3'!$C3,'SH2'!$A$3:$A$10000,0)))

Named Ranges
Name	Refers To	Cells
'SH2'!_FilterDatabase	='SH2'!$A$3:$G$7620	A3:B9

Cells with Data Validation
Cell	Allow	Criteria
C3:C9	List	='SH2'!$A$3:$A$26