INDEX and MATCH together do what VLOOKUP does, with two advantages: they can look to the left, and they survive a column being inserted.
The formula
=INDEX(C:C,MATCH(E2,A:A,0)) in column F
What each part does
MATCH(E2,A:A,0)
Finds which row the value sits on. The 0 forces an exact match.
INDEX(C:C,...)
Returns the value from that row of the answer column.
How it works, and what to watch for
MATCH finds which row your value sits on; INDEX then reads the answer from that row. The zero in MATCH forces an exact match, which is what you almost always want.
Because it works by column rather than by a counted position, inserting a column in the middle does not throw it off the way it throws VLOOKUP.
On Microsoft 365, XLOOKUP does all of this in a shorter form. INDEX and MATCH remain the reliable choice on older Excel.
Questions
Why choose this over VLOOKUP?
It can look left, and it does not break when someone inserts a column, because it points at columns directly rather than counting across from the left.
Is XLOOKUP a straight replacement?
On Microsoft 365 and Excel 2021, yes, and it reads more simply. Before that, INDEX and MATCH is the dependable pairing.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
