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]
