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?

 

[circledchicken]

Hi,

Perhaps try something like this and copy down (note you will need to press CTRL-SHIFT-ENTER rather than just ENTER as this is an array formula):

=LOOKUP(
    9.99999999999999E+307,
    IF('Trip Table'!A$2:A$20000 = C2,
      IF('Trip Table'!B$2:B$20000 >= A2,
        IF('Trip Table'!C$2:C$20000 <= B2,
          'Trip Table'!E$2:E$20000))))

 

[lgia]

Didn't work, Still need help with this lookup - match.

Im not sure what you mean by using CTRL-SHIFT-ENTER rather than just ENTER.

I assume the =Lookup(9.99999999999999E+307, Must have been a paste error. I see what you are getting at and tried $C2 as my lookup field . I am not sure why the lookup array is not working right, the first row in the example is not a match, the second row in the example is supposed to match. but when I watch it in the evaluate, it is looking at $A3 in the array and skipping $A2 which would have matched c2.

Perhaps there is something in the first lookup statement that would have made it work that didn't paste correctly. Help..Im on a deadline and this is so frustrating. I don't know why the array isn't look at the first position..

 

[Aladin Akyurek]

@lgia

Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key. If done properly, Excel will put a pair of { and } around the formula in recognition.

 

[lgia]

Huh! After pasting the formula and making the change on that variable that seemed to post incorrectly I tried the control shift enter in the formula window, now the formula works.. I never came across this before, I need to read up on this ! The lookup array problems I was having seem to work now. thanks guys

 

[circledchicken]

You're welcome.

Aladin has some amazing posts regarding the use of the big number 'BigNum' - here's one link for example:
http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html

You can also find out a lot about CTRL-SHIFT-ENTER (array formulas) by searching the forum / Googling / Youtube etc.

Good luck!

 

[lgia]

The formula provided doesn't seem to work. The if logic for dates simple >= doesnt work correctly. Provides inconsistent results.

Would you mind taking a quick look to see what is going on? If I use the If logic without the solution provided, it works. Inside the lookup array it doesn't.

 

[Aladin Akyurek]

The formula provided doesn't seem to work. The if logic for dates simple >= doesnt work correctly. Provides inconsistent results.

Would you mind taking a quick look to see what is going on? If I use the If logic without the solution provided, it works. Inside the lookup array it doesn't.

Would you provide the data for which the proposed formula does not deliver the desired results?

 

[lgia]

My last post- didnt get posted! The formula is working now. However, I have a question, when I use the control shift enter to bracket the formula it was not incrementing by row, it repeated the same formula A2 instead of A3 for the next line and so on. When I tried to modify the formula I would get an error that I cannot modify an array. When I removed the bracketing and didn't use the control shift enter, it works fine and increments the cells. Could I run into problems not using it?

 

[circledchicken]

Yes, it will not work correctly if you don't use CTRL-SHIFT-ENTER.

You only need to press CTRL-SHIFT-ENTER in the first cell you put the formula in (don't select a range of cells and press CTRL-SHIFT-ENTER). Then you can copy (or drag) the formula down from that first cell to fill the cells below.