How do you use the lookup function in Excel?

Answer 1

Get last entry by month and year

Generic formula 

=LOOKUP(2,1/(TEXT(dates,"mmyy")=TEXT(A1,"mmyy")),values)

Explanation 

To lookup the last entry in a table by month and year, you can use the LOOKUP function with the TEXT function. In the example shown, the formula in F5 is:

=LOOKUP(2,1/(TEXT($B$5:$B$13,"mmyy")=TEXT(E5,"mmyy")),$C$5:$C$13)

where B5:B13 and E5:E7 contain valid dates, and C5:C13 contains amounts.

How this formula works

Note: the lookup_value of 2 is deliberately larger than any values in the lookup_vector, following the concept of bignum.

Working from the inside out, the expression:

(TEXT($B$5:$B$13,"mmyy")=TEXT(E5,"mmyy"))

generates strings like "0117" using the values in column B and E, which are then compared to each other. The result is an array like this:

{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

where TRUE represents dates in the same month and year. The number 1 is then divided by this array. The result is an array of either 1's or divide by zero errors (#DIV/0!):

{1;1;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}

which goes into LOOKUP as the lookup array. LOOKUP assumes data is sorted in ascending order and always does an approximate match. When the lookup value of 2 can't be found, LOOKUP will match the previous value, so lookup will match the last 1 in the array.

Finally, LOOKUP returns the corresponding value in result_vector, which contains the amounts in C5:C13. 

Author 

Answer 2

VLOOKUP is an Excel function that retrieves data from a lookup table.

Cells D1 to E5 are the values from your lookup table.

Cells A2 to A6 is the data you are looking for in the lookup table. In Cell B2 enter the VLookup function with A2 is value you are looking for in cells D2 to E12 and return E2 to cell B2. Repeat for all cells in the B column. An example is shown below :