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

0
votes

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.

1
excelfunctionexcel-formula
what is the index match formula you tried that returned the 7/18/18 – Scott Craner
or even the vlookup that you used. we need something to help you. – Scott Craner
use the third criterion in the Match: =INDEX('AUSSIE Dly'!$A$3:$A$2000,MATCH($B$9,'AUSSIE Dly'!$A$3:$A$2000,0)-1) – Scott Craner

1 Answers

1
votes

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.