CODEWITHSUNDEEP
excelvlookup
eagle eagle
eagle eagle

Reputation: 35

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

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.

Upvotes: 0

Views: 10533

Answers (3)

Martin Router King
Martin Router King

Reputation: 155

For these cases where you have to find the next or previous closest result I prefer, 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

Upvotes: 0

Tom Sharpe
Tom Sharpe

Reputation: 34180

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

Upvotes: 1

Rowen Chumacera
Rowen Chumacera

Reputation: 529

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

Upvotes: 0

Related Questions