This is another formula that is like a VLOOKUP but much more flexible.

In an XLOOKUP you provide the formula with your lookup value. Then give it the lookup range that you want it to find that value in and then the return range. In this formula you can reference the entire column just like the VLOOKUP but you do not have to have the lookup values before the return values.

Here is an example formula:

=XLOOKUP(D2,B2:B8,A2:A8)

And this is it in action: