How to return a value from a different row

[sumandatta82]

I am trying to do the following:

I have a series of columns with dates in first column and values in column 3 and 5.

I want to check if date in first column matches my criteria and then return a value from column 3 but from a row below. I want to do this for column 5 also.

Vlookup does not work because it returns value from the same row.

What are some solutions ?

Suman

[Jonmo1]

Use Index Match instead of vlookup, and offset the rows..

Standard vlookup

=VLOOKUP(A1,'Other Sheet'!A1:B100,2,FALSE)

now you want to do the same thing, but return the value from 1 row below the match..

=INDEX('Other Sheet'!B$2:B$101,MATCH(A1,'Other Sheet'!$A$1:$A$100,0))

Notice the offset by 1 row between the 2 ranges.

[sumandatta82]

I need to input a value for the column number for INDEX I believe - it's returning a #NA error.

=INDEX('Other Sheet'!B$2:B$101,MATCH(A1,'Other Sheet'!$A$1:$A$100,0))

[Jonmo1]

No, you don't need a column Number with Index/Match.
Instead, you put the actual column reference that you want to 'return' a value from based on where the match was found.

If it's returning #N/A, it's because there was no exact match to A1 in the other sheet column A


Can you post a current formula that does what you want (except you want 1 row down)?

[sumandatta82]

Here is the vlookup version

VLOOKUP(Main!$B$1,'Calendar '!$E$30:$I$147,5,FALSE)

As you can see, it's a 5 column array and I want to check if column 1 has the correct matching date and then return a value from the 5th column but 1 row down

[Jonmo1]

Try

=INDEX('Calendar '!$I$31:$I$148,MATCH(Main!$B$1,'Calendar '!$E$30:$E$147,0))

[sumandatta82]

Thanks Jon. That works.