Hlookup and index possibly?

[lgia]

A	B	C	D	E
EmpID	Start Trip Date	End Trip Date	Old Charge	New Charge
6280	1/3/2015	5/4/2015	12	34
6293	3/3/2015	4/4/2015	24	44
6162	12/1/2015	2/2/2015	10	22

<tbody>
</tbody>

In my workbook I have a WS, in this example, called Trip Table.
I have a huge 20k row+worksheet that I want to match the EMP ID, compare start date and end date and if they fall into the range on the Trip table, leave the New charge in $E.

A	B	C	D
trip start date <tbody> </tbody>	trip end date <tbody> </tbody>	empid	new charge
5/15/2014 <tbody> </tbody>	5/30/2014 <tbody> </tbody>	6162 <tbody> </tbody>
1/12/2015 <tbody> </tbody>	2/2/2015 <tbody> </tbody>	6162 <tbody> </tbody>	22
2/2/2015 <tbody> </tbody>	2/23/2015 <tbody> </tbody>	8888 <tbody> </tbody>

<tbody>
</tbody>

Only row 2 would fit the criteria and therefore the new charge would appear in that cell.


I thought I could use an IF(and statement with vLookup to find the Emp ID and then compare the date range. vlookup doesnt work. I tried lookup and I cant even get a match which I don't understand, but I know even if lookup worked the rest of the logic doesn't make sense as it may not be on line 2.

my thought something like:
'=IF(AND(LOOKUP($c2,'Trip Table'!$A$2:$A$55,'Trip Table'!$e$2:$e$2000),$A2>='Trip Table'!$a2,'Trip Table!$c2>=$c2),$E2,""))

Probably something like an Hlookup with index match?? if I got that to work I still wouldn't know how to do the date compares.. can anyone help?

 

[lgia]

For some reason dragging the formula after the control shift enter wasnt incrementing for me last night.. It is incrementing now.

BUT the formula is not working!!! It should be straightforward in what I am trying to do and I got it to work in my test, but on the live data it is not. I am looking at the processing steps and Do not see what the problem is. This project is so overdue my boss is going to kill me, maybe I am just so nervous to see the error right in front of me. everything looks ok when watching it process the steps Here is the formula:

=LOOKUP(9.99999999999999E+307,
IF('Project Override Table'!A$2:A$30000 = U2,
IF(C2>='Project Override Table'!E$2:E$30000,
IF(D2<='Project Override Table'!F$2:F$30000,
'Project Override Table'!D$2:D$30000)),N2))

Data page has

C	D	N	O	U
Start Date	End Date	Exp Code	Override function here	Work ID
4/12/2015	4/15/2015	3039801	should show 3088533, but is showing 3039801	6162

<tbody>
</tbody>

Separate worksheet in workbook called
Project Override Table

A	B	C	D	E Start Week	F End Week
6162	FIOS	3039801	3088533	12/28/2014	5/16/2015

<tbody>
</tbody>

It is only supposed to find the value in A in the data sheet which is column U If it matches
Check to see if the start date and end date on the Project Override Table are within the range which is

4/12/2015

4/18/2015

<tbody>
</tbody>

Override Data range criteria is:

12/28/2014

5/16/2015

<tbody>
</tbody>

These dates fall between the range, so it is TRUE. Once these 3 conditions are met, put the value of column D in the project override into col O on the data sheet, if no match, then post the existing code in col N from the data sheet.

It should be a very simple thing.. I can't imagine what is going wrong. It would be wonderful if you could help me out of this mess!

 

[lgia]

Re: Hlookup and index possibly? -- Solution not working

again, my last post didn't appear, it think it may have timed out. I really need help. Yes, I was able to use the control shift enter to apply the brackets and this time it did increment the row numbers. HOWEVER the function is not working. It seemed to work in my test data. Im so late on this deliverable, Im in trouble, hope you can help. It seems like it should be quite straight forward. I watch in the evaluation window and do not see why it is not doing what it is supposed to.

=LOOKUP(
9.99999999999999E+307,
IF('Project Override Table'!A$2:A$30000 = U2,
IF(C2>='Project Override Table'!E$2:E$30000,
IF(D2<='Project Override Table'!F$2:F$30000,
'Project Override Table'!D$2:D$30000)),N2))

Project Override Table tab in workbook

A	B	C	D	E	F
Project ID	Project Name	Current exp code	Override Exp code	start Date range	end date range
6162	symph	3039801	3088533	12/28/2014	5/16/2015

<tbody>
</tbody>

DataTable tab in workbook

C	D	N	O	U
Start date	end date	Current exp code	Override Exp code	pROJECT ID
4/12/2015	4/18/2015	3039801	FORMULA HERE	6162

<tbody>
</tbody>

it is simply supposed to match the project ID and check data range to ensure the start and end are within the range and if so, put the Override Code (D) in column O in the data table, if not, put the existing code in column N in column O.

Is there any easier way of doing it? I don't know if there is something odd going on with the formula... I don't get an error, it looks like this should be working. Does my if statement with the date range make sense?

Now I see my previous post appeared with this one,,these two have duplicate info.

 

[lgia]

I dont see a way to attach the spreadsheet, can I do that? Down further I posted a line from the actual data, one from each of the spreadsheets in the workbook.
The array is looking at a spreadsheet with only 150 entries, the data file has 26,000. I assume the array should only be to 150, you will see I have 3000 in there, I ran into problems until I expanded the array..but since it isn't working right anyway, that doesnt mean I have it right! I could swear when I was pasting the formula down the raw I saw those values change. When I looked back to validate it worked, there were no updates. i could send a very short spreadsheet with specific examples if I could attach it.

 

[circledchicken]

I'm not sure I follow what you mean.

Yes, it might be easiest if you post a short spreadsheet with specific examples, the results you are getting and the results you expect.

You could create your sample spreadsheet on Excel Online (https://office.live.com/start/Excel.aspx) and share the link here.

 

[lgia]

hope this works never tried this before
https://onedrive.live.com/redir?res...590&authkey=!AISvmRRVB85L-vw&ithint=file,xlsx

 

[circledchicken]

I'm not sure how you are entering the formula provided.

This works for me in cell O2 (with CTRL-SHIFT-ENTER) and copied down:

=IFERROR(
    LOOKUP(
      9.99999999999999E+307,
      IF('Project Override Table'!A$2:A$30000 = U2,
        IF('Project Override Table'!E$2:E$30000 <= C2,
          IF('Project Override Table'!F$2:F$30000 >= D2,
            'Project Override Table'!D$2:D$30000)))),
    N2)

Here's a link to an updated file - you can download the file to view the solution:
https://onedrive.live.com/redir?page=view&resid=9CE8ECAB8B65DC03!2409&authkey=!AKobxP0qhDh4FFM

 

[lgia]

THank you for sticking in there with me. It WORKED!!!! This is the last thing I had to resolve on this tedious long task. THANK YOU THANK YOU!!!

 

[circledchicken]

You're welcome, thanks for the feedback.