CODEWITHSUNDEEP
excelfunctionexcel-formula
D Brown
D Brown

Reputation: 33

Return contents of the cell above the one indicated in VLOOKUP using ADDRESS, INDEX, MATCH, and/or CELL function

I am trying to use a date in Sheet 1 and using Vlookup to find this date on Sheet 2 and return the value/contents onto to a cell in sheet of the cell directly above that date in sheet 2. Sheet 1 Shows the Vlookup criteria for Sheet and the result which should be the cell directly above the VLOOKUP cell

Sheet 2 where we need to lookup the value of Sheet B9 and return the value on sheet 2 minus one cell.

This is the Formula I used that you can see

=INDEX('AUSSIE Dly'!$A$3:$A$2000,MATCH($B$9,'AUSSIE Dly'!$A$3:$A$2000)-1)

I have tried using Index and match but to no avail. Please help.

Upvotes: 0

Views: 606

Answers (1)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15561

You should use

=INDEX('AUSSIE Dly'!$A$3:$A$2000,MATCH($B$9,'AUSSIE Dly'!$A$3:$A$2000, 0)-1)

The added zero indicates using an Exact Match.

Upvotes: 1

Related Questions