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:



[TABLE="class: grid, width: 497"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Equipment plate no.[/TD]
[TD]old id number[/TD]
[TD]new id number[/TD]
[TD]location tag[/TD]
[TD]reason[/TD]
[/TR]
[TR]
[TD]3/7/18[/TD]
[TD]A5472H[/TD]
[TD]-[/TD]
[TD]2002[/TD]
[TD]Back cover[/TD]
[TD]item arrival[/TD]
[/TR]
[TR]
[TD]3/7/18[/TD]
[TD]A5472H[/TD]
[TD]-[/TD]
[TD]2003[/TD]
[TD]battery box[/TD]
[TD]item arrival[/TD]
[/TR]
[TR]
[TD]3/7/18[/TD]
[TD]A5472H[/TD]
[TD]-[/TD]
[TD]2004[/TD]
[TD]front cover[/TD]
[TD]item arrival[/TD]
[/TR]
[TR]
[TD]7/7/18[/TD]
[TD]A5472H[/TD]
[TD]2002[/TD]
[TD]2013[/TD]
[TD]Back cover[/TD]
[TD]protective screen[/TD]
[/TR]
[TR]
[TD]7/7/18[/TD]
[TD]A5472H[/TD]
[TD]2004[/TD]
[TD]2015[/TD]
[TD]front cover[/TD]
[TD]protective screen[/TD]
[/TR]
[TR]
[TD]13/7/18[/TD]
[TD]A0237E[/TD]
[TD]-[/TD]
[TD]2022[/TD]
[TD]Side screen [/TD]
[TD]Item arrival[/TD]
[/TR]
[TR]
[TD]13/7/18[/TD]
[TD]A0237E[/TD]
[TD]-[/TD]
[TD]2023[/TD]
[TD]back cover[/TD]
[TD]Item arrival[/TD]
[/TR]
</tbody>[/TABLE]

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.