INDEX and MATCH function not working (getting '#N/A')

[mneghassi]

Basic info: I'm trying to match the date in sheet 1 with the corresponding dollar amount in sheet 2, but I like to pull data from the row above, which represents one day earlier. I'm using INDEX and MATCH functions to accomplish this

Columns
SHEET1 Column A contains dates
SHEET2 Column A also contains dates
SHEET 2 Column B contains dollar amounts

My formulas
INDEX('SHEET2'!A:A,MATCH(A2,'SHEET 2'!B:B,0),-1)
INDEX('SHEET2'!A:A,MATCH(A3,'SHEET 2'!B:B,0),-1)
INDEX('SHEET2'!A:A,MATCH(A4,'SHEET 2'!B:B,0),-1)
and so on . . . .

Problem: I'm getting #N/As and have no idea why. I thought it was a formatting issue, but I don't think it is. I think the formula is constructed correctly. smh

 

[Special-K99]

You can't have a -1 column

Maybe

INDEX('SHEET2'!A:A,MATCH(A2-1,'SHEET 2'!B:B,0),1)

or

INDEX('SHEET2'!A:A,MATCH(A2,'SHEET 2'!B:B,0)-1,1)

Can only tell when I see some data

 

[mneghassi]

You can't have a -1 column

Maybe

INDEX('SHEET2'!A:A,MATCH(A2-1,'SHEET 2'!B:B,0),1)

or

INDEX('SHEET2'!A:A,MATCH(A2,'SHEET 2'!B:B,0)-1,1)

Can only tell when I see some data

Is that what I'm doing, telling excel to go move the column? I want to find the value (in dollars) in Sheet 2 column B that match with the date and go up one row in the same column. I'll try your suggestions right now.

 

[mikerickson]

If you are getting an #N/A error, I don't think it's the column argument that is the problem (that would cause a #VALUE error.) The #N/A suggests to me that the term being sought is not in Sheet2!B:B

But, if you are looking for the cell below the matching cell, Special-K99's second formula is one to use.

 

[mneghassi]

Here's how the data looks

Sheet 1, Column A
Jan 2, 2018
Jan 3, 2018
Jan 4, 2018
Jan 5, 2018
Jan 8, 2018
Jan 9, 2018
Jan 10, 2018
Jan 11, 2018
......

Sheet 2 Column A
Jan 2, 2018
Jan 3, 2018
Jan 4, 2018
Jan 5, 2018
Jan 8, 2018
Jan 9, 2018
Jan 10, 2018
Jan 11, 2018
....
Sheet 2 Column B
$500
$525
$812
$680
$529
$415
$679
$745

In column C in Sheet 1 is where I have the index/match functions. For Jan 3, 2018, I like it to pull $500 instead of $525. Does this make sense? Thanks in advanced.

 

[mneghassi]

If you are getting an #N/A error, I don't think it's the column argument that is the problem (that would cause a #VALUE error.) The #N/A suggests to me that the term being sought is not in Sheet2!B:B

But, if you are looking for the cell below the matching cell, Special-K99's second formula is one to use.

I tried it. It still gets #NA . I'm trying to go above the matching cell actually - so one cell above in the same column

 

[Special-K99]

Based on your last statement Jan3, 2018 wanting to return $500

in Sheet1!C2
=INDEX(Sheet2!B$1:B$1000,MATCH(A2,Sheet2!A$1:A$1000,0)-1,1)

does that

 

[mneghassi]

Based on your last statement Jan3, 2018 wanting to return $500

in Sheet1!C2
=INDEX(Sheet2!B$1:B$1000,MATCH(A2,Sheet2!A$1:A$1000,0)-1,1)

does that

It works. I reversed it. Thanks so so much!! This is the first time I used INDEX with MATCH. I feel empowered. hehe! Thanks again.