VLOOKUP or other function to allow autofill of cell based on 2 criteria from 2 different table

[atisyam]

Hello there. Apologies for the somewhat confusing title. I have attached an excel sheet to depict the example below. Here is what I want to achieve:

When user enter a new row for equipment plate no and location tag, the bolded number will be auto appear on the old id number column. These bolded numbers matched with their respective new id number.


User enter A5427H and back cover = excel auto fill 2013


User enter A5427H and battery box = excel auto fill 2003


User enter A0237E and back cover = excel auto fill 2023


These bolded numbers will depend on both equipment plate and location tag. Hence, I have used COUNTA function to ensure that old id number and new id number cannot be inserted prior to choosing the equipment plate no and location tag.


These bolded numbers can be changed. This is for troubleshooting purposes.

Hope that someone could help me with this. Thanksss.

 

[atisyam]

Just realized I can't attach the excel sheet and I've exceeded 10 mins to edit my posting. So here is the table:

A	B	C	D	E	F
Date	Equipment plate no.	old id number	new id number	location tag	reason
3/7/18	A5472H	-	2002	Back cover	item arrival
3/7/18	A5472H	-	2003	battery box	item arrival
3/7/18	A5472H	-	2004	front cover	item arrival
7/7/18	A5472H	2002	2013	Back cover	protective screen
7/7/18	A5472H	2004	2015	front cover	protective screen
13/7/18	A0237E	-	2022	Side screen	Item arrival
13/7/18	A0237E	-	2023	back cover	Item arrival

<tbody>
</tbody>

Anyway, the COUNTA function is on on column c and d. Hope to get some help.

 

[atisyam]

The question has been solved credits to Glen Kennedy from excelforum.

In C2 copy down: =IFERROR(LOOKUP(2,1/(($B$1:B1=B2)*($E$1:E1=E2)),$D$1:D1),"")

 

[RoryA]

Please read the forum rules on cross-posting and follow them in future. Thanks.