Excel - Read a value in one cell and replace it with a string from in another cell

Question

I have a massive spreadsheet of information I need to reconcile.

Sheet one has a list of reference numbers in columns -

AA1
AA2
AA3 etc.

Sheet two has a list of those references appended to a value in columns -

AA1 Triplo lego
AA2 Duplo lego
AA3 Small lego

I need a formula that will identify a value in sheet1, (AA1) search for that value in the list in sheet2 (AA1 Triplo lego) and populate the sheet1 cell with that value. (All the values in sheet 2 are unique)

I've looked at match and vloopup so far but can't seem to be able to work out how to do it. All help appropriated. Thank you.

Answer 1

If you need to find partial match in sheet 2 (AA1 Triplo lego written in one cell), and if your reference numbers are always 3 characters, then you can use below formula in cell B1. Drag it to the bottom.

=INDEX(Sheet2!$A$1:$A$600,SUMPRODUCT(ROW(Sheet2!$A$1:$A$600)*(--(A1=LEFT(Sheet2!$A$1:$A$600,3))*1)),1)