vlookup that also meets a date range criteria

To put into context, I am trying to lookup a purchase order number based on a number of criteria (Contractor id number, unit of measure etc). PO numbers are held from a start date to an end date and then a new PO number will be raised once the previous has expired. This means that there may be multiple results for the search criteria.

The crucial criteria that would result in the correct PO number being given is a week ending date (e.g 28/04/13). The sheet that i am looking up against contains all the required criteria but has a start date column and an end date column instead of a week ending date

I can concatinate all but the week ending date on both sheets and vlookup, but there will be multiple results and it just retruns the 1st result found. I need it to return the result based on the line that meets my date criteria (e.g. week ending date is less than end date column and greater than start date column)

Hope that makes sense?

Any help would be greatly appreciated!!
Thanks

Daaanj

Thought you might ask for that;

So i need to enter the correct PO numbers for each line on sheet 1 in column D. i could just search for the ID and UOM combination(as i have in this example in column D) but it will always just give the first PO number found that matches that criteria.

What i need to incorporate is the week ending date. So 'give me the PO number for the ID and UOM combination, but for the line that the week ending date falls into'

The results for my attached example on sheet 1 should be;

Row 2: Po lookup = 1
Row 3: Po lookup = 2
Row 4: Po lookup = 5

I hope thats a little clearer? Obviously the actual work book is much more complex and contains thousands of lines of data for multiple contractor ID's etc

Thanks again

Oh sorry... i'm looking againbst sheet 2 for the PO numbers

Thanks again

I do realise that sorry...

The question is more...'how do i enter the correct PO numbers in column D on sheet 1'

sheet 1 is basically timesheets that will be received and so always have a week ending date. I need to assign the corcet PO numbers and can only lookup against a report that is similar to that on sheet 2 of my example.

Each file contains multiple week ending dates and we current use a very long winded process of working through 1 week ending date at a time. We add a week ending date to sheet 2 and a column that states which lines are 'valid' for that week ending date. we can then look for the 'valid' PO for our combination.

We stand to save a huge amount of processing time if we can find a beter way of doing it

Originally Posted by greggy99

you can concatenate the po=(hour:minutes:seconds+Date+id)

I'm not sure i understand your suggestion sorry greggy.

I can't use the week ending date in the concatinate as the sheet im looking against doesn't contain week ending dates and so won't find a match. It contains a start and end date and so i need to search for my combination, but include a way to only give the po number for the line that the week ending date falls bewteen the start and end date (week ending is greater than start date & less than end date)

A coleague suggested it might require an array table? not sure how they work though.

I was hoping there'd be something simple like an 'If' formula

i didn't mean array table :/

*a colleague suggested it might work using index?

It's had me stumped for 2 days now

Originally Posted by greggy99

as you said 1.)"how you get the correct po in sheet1?"
answer=by getting the unique identifier to lookup.
how=in your concatinated column(sheet2) you can put the id+date included the time(hour+minutes+seconds) & _
so you can obtain unique key for each transactions...

after you do that you can now use vlookup in sheet1 using your concatenated unique key in sheet2....hope it helps....

Really appreciate you trying to help greggy but i still don't understand your suggestion

The dates in sheet 2 are start dates in one column and end dates in the other. The date i need to lookup is a week ending date that must fall between the start and end date. It will never work in a concatinate becuase the week ending date will never match the dates in sheet 2?

I need to find the corect PO number for my combination of id+UOM and week ending date <end date&>start date

Would you mind showing me what you mean on the attached example?

Ok... so you have worked out what date falls smack bang in the middle of the start and end date b working out the difference, halving it (to get half of the difference, therefore the middle number) and then adding it to the start date. We now have the date in the middle of my range (start and end date). You then seem to have changed my week ending dates to those dates and then done your lookup?

i think you misunderstand what i'm trying to achieve Or i just don;t understand your solution

Let me try and give a different example that presents the same problem

A contractor who works as a brew boy in London has submitted his timesheet for april on sheet 1. You can see he has worked 37 hours each week (The week ending date is the sunday for each week worked)

We need to work out how much he should be paid for the month

Sheet 2 contains a "rate card". You can see that people working as a brew boy get paid more in London than they do in Blackburn. You'll also notice that there was an increase to the pay rate from 15/04/13 (an extra £ per hour)

So using a formula, We need to add the correct pay rate on sheet 1 in column F. Column G then multiplies his pay rate by the hours worked to give his weekly pay and therefore monthly pay as a total.

Thanks again.. We may not understand each other but i still really apreciate the help

Thanks Greggy... Unfortunatley its still not quite what i need.

I won't normally know that a rate has increased. I need to rely on the formula pulling back the correct rate for that week ending date and so i wouldn't know the condition needed for column F. Rates can change at any time.

Thanks again though for trying. You've given my query a good go! and do really appreciate it.

Out of interest, i notice your lookup in column H contans the letters 'PHRN' in the table array field? The table array is the range of cells in which the data is retrieved from.... What does the PHRN stand for and how does that work? It's not really imprortant, so don't worry about explaining if it is complicated. It's just something i've never seen before and thought it's an opportunity to learn something new

Thanks