Need help with populating a cell with a number, based on an approximate date match

[Stigdu]

Hi all,

I have a spreadsheet with 2 tabs, the first is '2016' and the second is 'Fuel.'

I need to auto-populate a cell, K3, on the '2016' sheet with an amount of money which has already been input on the 'Fuel' sheet.

To work out what value needs to be returned, I need an exact name match of 2016!D3 (this is the cell where the engineer's name is listed on the first sheet) with 'Fuel'!column C, and I also need an approximate date match of 2016!K2 with 'Fuel'!column A

e.g.

Sheet 1:

H I J K
15/12/15 22/12/15 29/12/15 04/01/16
xxx xxx xxx £fuel amount (this get autopopulated thanks to the formula entered here)



Sheet 2:

A B C D E F
3/1/16 John Smith £35
10/1/16 John Smith £42
17/1/16 Pete Jones £19


I've Googled and variously tried a vlookup, an INDEX, MATCH and also a SUMPRODUCT (this one came the closest to working) but none of them work.

Can anyone please advise?
Many thanks!

[Pete_UK]

It would help if you attached a sample Excel workbook - the FAQ describes how to.

Pete

[Stigdu]

Will do. I seem to have created 2 threads by mistake. I've marked 1 as solved (although it isn't!)

[Pete_UK]

You can't delete threads, but you could edit the text in that other thread and delete most of it (just leave a comment like "Duplicated post").

Hope this helps.

Pete

[Stigdu]

Example attached.

So basically I'm having to manually copy the fuel spending amount in column 'E' (Fuel) into the relevant row 4 on the first sheet (2016) depending on the date.

Sometimes the date won't be an exact match and might be 2 or 3 days out.

I'd like row 4 on the first tab to auto-populate based on the data that's been entered into the 'Fuel' tab.

Hope that makes sense.

[Pete_UK]

OK, I'm just about to go out now, but I'll take a look when I get back (if nobody else has answered).

Pete

[shukla.ankur281190]

Try

C4 and drag right the cells.

Please Login or Register  to view this content.

[Stigdu]

That's almost it - the only thing with that is that the date has to match exactly. If it doesn't match, then I get "£0.00" output in the fuel amount Row on sheet 1....

[shukla.ankur281190]

I Don't know what you did.

Please check the attached file and it is working there.