tc
Steve wrote:
Thanks for your help VLOOKUP was the answer once I had said 'no sort':
VLOOKUP(LEFT(C18;3);A$8:D$14;4;0)
--- Anthony Chilco <[EMAIL PROTECTED]> wrote:
Hi Steve,
I didn't look closely enough at your formula. You can do the job nicely with vlookup. Save yourself the hassle of editing the copies with the absolute reference, but change the last range to 4. This is an index into the row that has the first instance of the search criterion. In other words searching for 'Wed' takes you to row 10, the 4 takes you to column D and the result is 42.
Like this: VLOOKUP(LEFT(C18;3);A$8:D$14;4)
Lookup is useful when the result data is not in line with the search criteria.
tc
Steve wrote:
Hitc,
Yes I have noticed and edited each cell to make sure the formula points to
the
correct cells/rows, still no joy.
Let me try to explain what is happening:
A........D
8 Mon......20 9 Tue......13 10 Wed......42 11 Thu......15 12 Fri......31 13 Sat......26 14 Sun......39
so if C18 is "Fri" "Sat" or "Sun" with A8:A14 A12 & thus D12 are not found
by
LOOKUP only "Mon" "Tue" "Wed" "Thu" will but if I change to A9:A14 only
"Tue"
Wed" "Thu" "Fri" but not "Mon" "Sat" or "Sun". If you can see the pattern
you
can understand why I ask if LOOKUP is only capable of looking at 4 rows!
Steve
--- Anthony Chilco <[EMAIL PROTECTED]> wrote:
Hi Steve,
If you're copying the formula, you need to make the search range absolute. Otherwise the start point moves down as you copy. Do this:
LOOKUP(LEFT(C18;3);A$8:A$14;D$8:D$14)
tc
Steve wrote:
Is there a limit to the size of the vector it can lookup?
LOOKUP(LEFT(C18;3);A8:A14;D8:D14)
This will only lookup in the first four rows specified within the vector. Column A8:A14 is the days of the week (first 3 letters), if C18 is Mon,
Tue,
Wed, Thu then it will find the correct data in column D. If C18 is Fri,
Sat,
Sun then no data is found UNLESS I change the first part of the vector
A8:A14
to the row at least 4 days before the day being looked-up then the correct
data
in column D will be found. Is there a limit to the size of the vector
LOOKUP
can search? Am I attempting something LOOKUP is not designed for? I
would
be
grateful for any pointers.
Thanks, Steve
Send instant messages to your online friends http://uk.messenger.yahoo.com
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Send instant messages to your online friends http://uk.messenger.yahoo.com
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Send instant messages to your online friends http://uk.messenger.yahoo.com
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
