Shifting the value of a Vlookup in Excel

Solution 1:
@user3561813 has provided an explanation for a possible solution. By utilizing MATCH, the position of the last entry in ColumnB is determined. Then, by moving up seven rows and using INDEX, the content of that row in ColumnB is retrieved.
Solution 2:
Another approach involves using a single function where the “return vector” is shifted by seven rows.
Solution 3:
An alternative suggestion is to modify the lookup to find the last day in the list. By using a specific formula, the value of the last day can be obtained. This formula can then be used to retrieve the last value for that day. To obtain the value from seven days earlier, simply subtract seven from the results of the LOOKUP formula.
Solution 4:
The function that is being referred to as “the” is associated with a Range object, which represents a cell.

Question:

I am attempting to retrieve a value that is located one row below the
specific cell
in an Excel spreadsheet. As an example, it uses Vlookup to search for each name and I need to obtain the value for total activity.

This is what I have so far:

=offset(vlookup(A4,'Case Manager Reports - Jun'!A:B,2,0), 1, 0)

Is there a valid method to accomplish this as I understand that
Vlookups return
represents the value of the cell rather than its address, which is why the formula fails?


Solution:

Achieving this can be done by utilizing Index/Match+1, as demonstrated below:


=INDEX('Case Manager Reports - Jun'!B:B,MATCH(A4,'Case Manager Reports - Jun'!A:A,0)+1)

Frequently Asked Questions