VLOOKUP or functions for autofill of cell based on criterias from different column

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 on column c and d 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.

Originally Posted by Glenn Kennedy

In c2, copied down:

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

Hi Glenn

Amazing. Works exactly as per intended!

However, don't mind just to add on to this thread. As these numbers can be changed, I would like to put conditional formatting by means of making the font colour to be red. As the auto return value is exact, once the user changed it to another value, it will not be exact anymore. I highlighted the whole of column C and inserted this custom formulae:

=VLOOKUP($C1,$D1,3,FALSE)

And nothing happened to the font if I changed the numbers to a different value.

I am not good with excel and hope you can help me out. Thanksss.

Originally Posted by Glenn Kennedy

However, don't mind just to add on to this thread. As these numbers can be changed, I would like to put conditional formatting by means of making the font colour to be red. As the auto return value is exact, once the user changed it to another value, it will not be exact anymore. I highlighted the whole of column C and inserted this custom formulae:

I don't understand.

Red: which numbers do you mean?

Orange: the font colour of which column?

Green: which cell(s) is/are "it"?

If you want excel to "remember" a value in a cell that you have over-written, it can not do that without VBA. That is a new question and needs a new thread.

Hai Glenn. Sorry for the late reply.

Red: These numbers refer to the numbers that was initially autofilled by the function, but changed by the user. These numbers would be numebrs in column C.
Orange: The column is column C. This the same column as per above. So basically once the user has changed the number, the new number (the number that the user typed in) will change red.
Green: These cells are the cells in column C.

Hhehehe, and yeah its true that I do want excel to 'remember' the value that was overwritten. So knowing that the value is overwritten, the entered value in column C will have a red font colour.

Noted aboout the VBA option. Thanks for the past replies and providing a solution to my enquiry.