failing to formulate a nested If DateDif - pls help!

[BEX GREEN]

Noobie here so pls be gentle! Just looking for some help pretty please, as there is no one around who can help me with this?

I have two sheets of data (one called "Raised", one called "Responses"), both have order numbers and dates plus a bunch of other info. I'm trying to do a nested "if datedif" to work out difference in days between the two sheets but only if there is a match on the order number. so I think I worked it out like this -

=IF(RESPONSES!$C:$C=RAISED!D28, DATEDIF(RESPONSES!A29, RAISED!B28,"d"), "")

Column C on Responses = Order number
Column D on Raised = Order number
then columns B on Raised and C on Responses are the date fields (which have been formatted to short dates).

I'm only getting "" results, even though I know there are matching order numbers (I did a separate vlookup to check that)

Help me forum peoples!! you are my only hope!

[DannyJ]

=(VLOOKUP(RESPONSES!A:A,RESPONSES!A:B,2)-VLOOKUP(RAISED!A:A,RAISED!!A:B,2))

That should work. Just change the references if necessary. Tried to emulate it as best I could.Format the cell to Number. It will give you number of days.

If you don't want it to include the referenced day then just put a -1 on the end.

[BEX GREEN]

Thanks Danny (I would never have worked that one out by myself! - but I just got a #VALUE! or #n/a! instead of a result?

[DannyJ]

Can you upload a copy of the spreadsheet? Would probably be easier. Makes sure all the cell references are correct too.

Just to go over the cell references. How can response have a date and order number in same column?

[BEX GREEN]

sure - can just explain how to upload it to the forum please? never done that before

[DannyJ]

Click go advanced > manage attachments and then upload from there.

[DannyJ]

I am heading out from work and won't have access to a computer for the rest of the day. To try and help you I will explain what the formula does:

A:A references the order numbers (Change that for each sheet) - A:B references the 'table' so that will need to include both the order number and date in it. The '2' on the end is a column reference. This needs to point to the date from left to right (Example: If the range is B:D and date is in B then it will be 1. If date is in D it will be 3.

Sorry I can't help more.

[BEX GREEN]

ok - I uploaded the file

[BEX GREEN]

the bit I'm working on is on "RAISED" column K.

ahh just spotted your reply. ok - well thanks for your help danny! have a good day

[BEX GREEN]

Is anyone else able to assist please? I'm getting mighty frustrated here! - updated document uploaded

thanks

[BlindAlley]

Change the cell colour where you need the end result formula to green, enter the result that you expecting, change the colour in the 1st cell of the 'lookup' columns to red.

Then repost your workbook.

That way someone can easily determine exactly where and what to look for and work out the formula required.

[BEX GREEN]

My apologies - it was perhaps a little confusing. So here's the revision.

The aim is to match the order numbers in red on "Raised tab" with order numbers in red on "Responses tab".

Where there is a match - what is the difference in days between orange on "Raised" and blue on "Respones" - put this result in the green column on "Raised" against the same line as the order number it matched with.

Where there is no match - leave the green cell empty.

[BlindAlley]

A solution using INDEX MATCH which has the bonus of returning the response date, the response time, and leaves the cells blank when no match is found.

[BEX GREEN]

OMG THATS BRILLIANT!

thank you so very very much for your help!! have a fantastic weekend!

[BlindAlley]

OMG THATS BRILLIANT!

thank you so very very much for your help!! have a fantastic weekend!

Thanks for the feedback, glad it worked for you, especially seeing as I am a Burnley fan :-).

PS. You can also use the INDEX MATCH to return the Liability values as well.