Vlookup Help

[CCkfm2000]

I need help with vlookup.

I have a spreadsheet with the following information I need to key in

In cell F2 I need to enter Total Hours 8
In cell F3 I need to enter Total Plts 104
In cell F4 I need to enter P/P Hour 13
In cell F5 I need to enter Start Time 7

I can have up to 5 different entries in the spreadsheet any one time.

The problem is when I do a vlookup in cells N9 to O49
I should get the following

N9 = Monday Day O9 = 09:00
N10 = Monday Day O10 = 11:00
N11 = Monday Day O11 = 13:00
N12= Monday Day O12 = 15:00

But I get in cell N12 = Friday Night, O12 = 06:00

The vlookup in column B need to get ever 26th value.


Here is some more data to enter for example

Line A Line B Line C Line D Line E
Total Hours 83 37 36 8 0
Total Plts 149 124 119 104 0
P/P Hour 2 3 3 13 0
Start Time 41 37 91 7 0


i've attached a test file for info.

Any help will much appreciated

Many Thanks
:bug:

[mark007]

Change your formula to:


=VLOOKUP(M9,$B$9:$E$128,4,FALSE)

i.e. use false at the end. The entries below row 4 are then N/A's as 130+ are never found but you could test for this and display something else using iserror() if you wanted to.

:)

[CCkfm2000]

Thanks mark007 for you quick response, having changed the formula to the you suggested and entering the following

Line A Line B
83 37
149 124
2 3
41 37

I get in cell N 9 #N/A O 9 #N/A :doh:
cell N 10 is ok
cell N 11 i get #N/A


thanks

[mark007]

Well in that case 26 isn't found, what would you want it to return?

[ALe]

I didn't get exactly your problem but maybe this could be of help.
In the lookup formula change the last argument from TRUE to FALSE. I did it and I got the results you expected.

[mark007]

That won't work as that's what he had originally (it defaults to true).

:)

[ALe]

Yes Mark007. Now I got the problem and I see you're right. Thank you. This has been the chance to understand the meaning of that TRUE/FALSE in the VLookUp formula (I never looked up it).

[CCkfm2000]

thanks for your help on this...

i need to lookup 26, 52 and on so.
e.g if i can't get 26 then i need to get the next close number equaling 26 up or down.

give or take a few...

[mark007]

So it can be either up or down. Will have to think about that one!

[mark007]

Ok, try this array formula approach, think it just about does the job though I'm sure there must be a neater approach!

:)

[CCkfm2000]

thanks mark007... your a star. :clap: looks like you have crack this problem

i've been at this for about 2 weeks now..

thanks for all the help to everybody.

i'm going to mark this as solved now.....

cckfm2000 :friends:

[mark007]

No problem. If you come across any scenarios with problems, post back and I'll take another look!

:)

[CCkfm2000]

thanks again mark007