Find exact date or nearest date in a range of dates using excel formula

0
votes

I tried to find the Inv Cost. I used vlookup to used it. Please refer below image for more details.

Sheet1

As you can see from the sheet1 I'm taking Actual finish date and product number to compare from sheet2 product number & cost date.

Sheet2

The excel formul find the exact date and number display on Inv Cost column. If can't find, Inv Cost should display the nearest date in the range of dates in excel sheet.

But my excel vlookup formul show N/A. my excel formula is below:

=VLOOKUP(B2&A2,Sheet2!C:D,2,FALSE)

Please advise on this.Thanks.

3
excelvlookup

3 Answers

1
votes

If you wanted to find the nearest date, you would need an array formula like this one

=INDEX(Sheet2!D$2:D$10,MATCH(TRUE,IF(B2=Sheet2!A$2:A$10,ABS(Sheet2!B$2:B$10-A2))=MIN(IF(B2=Sheet2!A$2:A$10,ABS(Sheet2!B$2:B$10-A2))),0))

Must be entered with CtrlShiftEnter

enter image description here

0
votes

Try changing the last parameter of the VLOOKUP function to TRUE

=VLOOKUP(B2&A2, Sheet2!C:D, 2, TRUE)

Setting it to FALSE looks for an exact match.

However, it gives me a different result from what you're expecting. enter image description here

0
votes

For these cases where you have to find the next or previous closest result I preffer, and I think it's the most suitable, using INDEX/MATCH.

The formula would be like this.... try it...

=INDEX('Sheet2'!D2:D5;MATCH(CONCATENATE(B2;A2);'Sheet2'!C2:C5;-1))

Write this formula on sheet1 and scroll down, let me know if it works or not :D