condition Lookup help

Hi All

Being an excel novice I might be going at this the wrong way but...

I am trying to look up 2 values from sheet one and find the answer on sheet
2 but can't work it out.

Find value (date)&(team) from sheet1 and lookup the result on sheet 2 (F2)

E.G look for date 13/8/05 and team Arsenal from sheet 1 on sheet 2 and
display result in column 6 (F2) on sheet 2

Sheet1
Date = i.e. A1 13/08/2005
Team = i.e. A2 "Arsenal"

Sheet 2
DATE HOME AWAY score h score a h result
a result = column headings
A2 B2 C2 D2 E2
F2 G2
13/08/05 Arsenal Bolton 1 0
HW AL

Anyone got any ideas?

Tried VLOOKUP but I can only get it to look for one condition i.e. date I
can't do both!

Any help, pointers greatly appreciated

Paul T

=INDEX(Sheet2!$F$2:$F$1000,MATCH($A1&$A2,Sheet2!$A$2:$A$1000&Sheet2!$B$2:$B$
10000,0))

This is an array formula, so commir with Ctrl-Shift-Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul T" <[email protected]> wrote in message
news:[email protected]...
> Hi All
>
> Being an excel novice I might be going at this the wrong way but...
>
> I am trying to look up 2 values from sheet one and find the answer on
sheet
> 2 but can't work it out.
>
> Find value (date)&(team) from sheet1 and lookup the result on sheet 2 (F2)
>
> E.G look for date 13/8/05 and team Arsenal from sheet 1 on sheet 2 and
> display result in column 6 (F2) on sheet 2
>
> Sheet1
> Date = i.e. A1 13/08/2005
> Team = i.e. A2 "Arsenal"
>
> Sheet 2
> DATE HOME AWAY score h score a h result
> a result = column headings
> A2 B2 C2 D2 E2
> F2 G2
> 13/08/05 Arsenal Bolton 1 0
> HW AL
>
> Anyone got any ideas?
>
> Tried VLOOKUP but I can only get it to look for one condition i.e. date I
> can't do both!
>
> Any help, pointers greatly appreciated
>
> Paul T
>
>

Hi Bob

Thank for your help,

I am nearly there!

Got the 1st 3 lines to work (all spot on) but the rest are #N/A

Looked at and even retyped from scratch but still #N/A on others

Any ideas why?

Thanks again.

Paul


"Bob Phillips" <[email protected]> wrote in message
news:OQpO%[email protected]...
> =INDEX(Sheet2!$F$2:$F$1000,MATCH($A1&$A2,Sheet2!$A$2:$A$1000&Sheet2!$B$2:$B$
> 10000,0))
>
> This is an array formula, so commir with Ctrl-Shift-Enter.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Paul T" <[email protected]> wrote in message
> news:[email protected]...
>> Hi All
>>
>> Being an excel novice I might be going at this the wrong way but...
>>
>> I am trying to look up 2 values from sheet one and find the answer on
> sheet
>> 2 but can't work it out.
>>
>> Find value (date)&(team) from sheet1 and lookup the result on sheet 2
>> (F2)
>>
>> E.G look for date 13/8/05 and team Arsenal from sheet 1 on sheet 2 and
>> display result in column 6 (F2) on sheet 2
>>
>> Sheet1
>> Date = i.e. A1 13/08/2005
>> Team = i.e. A2 "Arsenal"
>>
>> Sheet 2
>> DATE HOME AWAY score h score a h
>> result
>> a result = column headings
>> A2 B2 C2 D2 E2
>> F2 G2
>> 13/08/05 Arsenal Bolton 1 0
>> HW AL
>>
>> Anyone got any ideas?
>>
>> Tried VLOOKUP but I can only get it to look for one condition i.e. date I
>> can't do both!
>>
>> Any help, pointers greatly appreciated
>>
>> Paul T
>>
>>
>
>

That means the MATCH failed Paul, which suggests that the data has something
that isn't obvious, such as spaces in the names.

Post say the first 6, both sheets, so that I can see if I get the same.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul T" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob
>
> Thank for your help,
>
> I am nearly there!
>
> Got the 1st 3 lines to work (all spot on) but the rest are #N/A
>
> Looked at and even retyped from scratch but still #N/A on others
>
> Any ideas why?
>
> Thanks again.
>
> Paul
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:OQpO%[email protected]...
> >
=INDEX(Sheet2!$F$2:$F$1000,MATCH($A1&$A2,Sheet2!$A$2:$A$1000&Sheet2!$B$2:$B$
> > 10000,0))
> >
> > This is an array formula, so commir with Ctrl-Shift-Enter.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Paul T" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi All
> >>
> >> Being an excel novice I might be going at this the wrong way but...
> >>
> >> I am trying to look up 2 values from sheet one and find the answer on
> > sheet
> >> 2 but can't work it out.
> >>
> >> Find value (date)&(team) from sheet1 and lookup the result on sheet 2
> >> (F2)
> >>
> >> E.G look for date 13/8/05 and team Arsenal from sheet 1 on sheet 2 and
> >> display result in column 6 (F2) on sheet 2
> >>
> >> Sheet1
> >> Date = i.e. A1 13/08/2005
> >> Team = i.e. A2 "Arsenal"
> >>
> >> Sheet 2
> >> DATE HOME AWAY score h score a h
> >> result
> >> a result = column headings
> >> A2 B2 C2 D2
E2
> >> F2 G2
> >> 13/08/05 Arsenal Bolton 1 0
> >> HW AL
> >>
> >> Anyone got any ideas?
> >>
> >> Tried VLOOKUP but I can only get it to look for one condition i.e. date
I
> >> can't do both!
> >>
> >> Any help, pointers greatly appreciated
> >>
> >> Paul T
> >>
> >>
> >
> >
>
>

Hi Bob

Sussed it - thanks to your suggestion.

Spaces in name &/or after !!! (Had to re-do all names so all same format)

And it worked a treat !

Thank you very much.

Kind Regards
Paul

"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> That means the MATCH failed Paul, which suggests that the data has
> something
> that isn't obvious, such as spaces in the names.
>
> Post say the first 6, both sheets, so that I can see if I get the same.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Paul T" <[email protected]> wrote in message
> news:[email protected]...
>> Hi Bob
>>
>> Thank for your help,
>>
>> I am nearly there!
>>
>> Got the 1st 3 lines to work (all spot on) but the rest are #N/A
>>
>> Looked at and even retyped from scratch but still #N/A on others
>>
>> Any ideas why?
>>
>> Thanks again.
>>
>> Paul
>>
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:OQpO%[email protected]...
>> >
> =INDEX(Sheet2!$F$2:$F$1000,MATCH($A1&$A2,Sheet2!$A$2:$A$1000&Sheet2!$B$2:$B$
>> > 10000,0))
>> >
>> > This is an array formula, so commir with Ctrl-Shift-Enter.
>> >
>> > --
>> >
>> > HTH
>> >
>> > RP
>> > (remove nothere from the email address if mailing direct)
>> >
>> >
>> > "Paul T" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi All
>> >>
>> >> Being an excel novice I might be going at this the wrong way but...
>> >>
>> >> I am trying to look up 2 values from sheet one and find the answer on
>> > sheet
>> >> 2 but can't work it out.
>> >>
>> >> Find value (date)&(team) from sheet1 and lookup the result on sheet 2
>> >> (F2)
>> >>
>> >> E.G look for date 13/8/05 and team Arsenal from sheet 1 on sheet 2 and
>> >> display result in column 6 (F2) on sheet 2
>> >>
>> >> Sheet1
>> >> Date = i.e. A1 13/08/2005
>> >> Team = i.e. A2 "Arsenal"
>> >>
>> >> Sheet 2
>> >> DATE HOME AWAY score h score a h
>> >> result
>> >> a result = column headings
>> >> A2 B2 C2 D2
> E2
>> >> F2 G2
>> >> 13/08/05 Arsenal Bolton 1 0
>> >> HW AL
>> >>
>> >> Anyone got any ideas?
>> >>
>> >> Tried VLOOKUP but I can only get it to look for one condition i.e.
>> >> date
> I
>> >> can't do both!
>> >>
>> >> Any help, pointers greatly appreciated
>> >>
>> >> Paul T
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Good stuff Paul. Gooners man?

Bob


"Paul T" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob
>
> Sussed it - thanks to your suggestion.
>
> Spaces in name &/or after !!! (Had to re-do all names so all same format)
>
> And it worked a treat !
>
> Thank you very much.
>
> Kind Regards
> Paul
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> > That means the MATCH failed Paul, which suggests that the data has
> > something
> > that isn't obvious, such as spaces in the names.
> >
> > Post say the first 6, both sheets, so that I can see if I get the same.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Paul T" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi Bob
> >>
> >> Thank for your help,
> >>
> >> I am nearly there!
> >>
> >> Got the 1st 3 lines to work (all spot on) but the rest are #N/A
> >>
> >> Looked at and even retyped from scratch but still #N/A on others
> >>
> >> Any ideas why?
> >>
> >> Thanks again.
> >>
> >> Paul
> >>
> >>
> >> "Bob Phillips" <[email protected]> wrote in message
> >> news:OQpO%[email protected]...
> >> >
> >
=INDEX(Sheet2!$F$2:$F$1000,MATCH($A1&$A2,Sheet2!$A$2:$A$1000&Sheet2!$B$2:$B$
> >> > 10000,0))
> >> >
> >> > This is an array formula, so commir with Ctrl-Shift-Enter.
> >> >
> >> > --
> >> >
> >> > HTH
> >> >
> >> > RP
> >> > (remove nothere from the email address if mailing direct)
> >> >
> >> >
> >> > "Paul T" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> Hi All
> >> >>
> >> >> Being an excel novice I might be going at this the wrong way but...
> >> >>
> >> >> I am trying to look up 2 values from sheet one and find the answer
on
> >> > sheet
> >> >> 2 but can't work it out.
> >> >>
> >> >> Find value (date)&(team) from sheet1 and lookup the result on sheet
2
> >> >> (F2)
> >> >>
> >> >> E.G look for date 13/8/05 and team Arsenal from sheet 1 on sheet 2
and
> >> >> display result in column 6 (F2) on sheet 2
> >> >>
> >> >> Sheet1
> >> >> Date = i.e. A1 13/08/2005
> >> >> Team = i.e. A2 "Arsenal"
> >> >>
> >> >> Sheet 2
> >> >> DATE HOME AWAY score h score a h
> >> >> result
> >> >> a result = column headings
> >> >> A2 B2 C2 D2
> > E2
> >> >> F2 G2
> >> >> 13/08/05 Arsenal Bolton 1 0
> >> >> HW AL
> >> >>
> >> >> Anyone got any ideas?
> >> >>
> >> >> Tried VLOOKUP but I can only get it to look for one condition i.e.
> >> >> date
> > I
> >> >> can't do both!
> >> >>
> >> >> Any help, pointers greatly appreciated
> >> >>
> >> >> Paul T
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Hi Bob

No For my sins... Forest :-) Who are venturing in unknown territory ...
unfortunately it's called League 1

I mentioned Arsenal as 1st team alphabetically! in the prem league, I am
working on.

Cheers
Paul

"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> Good stuff Paul. Gooners man?
>
> Bob
>
>
> "Paul T" <[email protected]> wrote in message
> news:[email protected]...
>> Hi Bob
>>
>> Sussed it - thanks to your suggestion.
>>
>> Spaces in name &/or after !!! (Had to re-do all names so all same format)
>>
>> And it worked a treat !
>>
>> Thank you very much.
>>
>> Kind Regards
>> Paul
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:%[email protected]...
>> > That means the MATCH failed Paul, which suggests that the data has
>> > something
>> > that isn't obvious, such as spaces in the names.
>> >
>> > Post say the first 6, both sheets, so that I can see if I get the same.
>> >
>> > --
>> >
>> > HTH
>> >
>> > RP
>> > (remove nothere from the email address if mailing direct)
>> >
>> >
>> > "Paul T" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi Bob
>> >>
>> >> Thank for your help,
>> >>
>> >> I am nearly there!
>> >>
>> >> Got the 1st 3 lines to work (all spot on) but the rest are #N/A
>> >>
>> >> Looked at and even retyped from scratch but still #N/A on others
>> >>
>> >> Any ideas why?
>> >>
>> >> Thanks again.
>> >>
>> >> Paul
>> >>
>> >>
>> >> "Bob Phillips" <[email protected]> wrote in message
>> >> news:OQpO%[email protected]...
>> >> >
>> >
> =INDEX(Sheet2!$F$2:$F$1000,MATCH($A1&$A2,Sheet2!$A$2:$A$1000&Sheet2!$B$2:$B$
>> >> > 10000,0))
>> >> >
>> >> > This is an array formula, so commir with Ctrl-Shift-Enter.
>> >> >
>> >> > --
>> >> >
>> >> > HTH
>> >> >
>> >> > RP
>> >> > (remove nothere from the email address if mailing direct)
>> >> >
>> >> >
>> >> > "Paul T" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> Hi All
>> >> >>
>> >> >> Being an excel novice I might be going at this the wrong way but...
>> >> >>
>> >> >> I am trying to look up 2 values from sheet one and find the answer
> on
>> >> > sheet
>> >> >> 2 but can't work it out.
>> >> >>
>> >> >> Find value (date)&(team) from sheet1 and lookup the result on sheet
> 2
>> >> >> (F2)
>> >> >>
>> >> >> E.G look for date 13/8/05 and team Arsenal from sheet 1 on sheet 2
> and
>> >> >> display result in column 6 (F2) on sheet 2
>> >> >>
>> >> >> Sheet1
>> >> >> Date = i.e. A1 13/08/2005
>> >> >> Team = i.e. A2 "Arsenal"
>> >> >>
>> >> >> Sheet 2
>> >> >> DATE HOME AWAY score h score a h
>> >> >> result
>> >> >> a result = column headings
>> >> >> A2 B2 C2 D2
>> > E2
>> >> >> F2 G2
>> >> >> 13/08/05 Arsenal Bolton 1 0
>> >> >> HW AL
>> >> >>
>> >> >> Anyone got any ideas?
>> >> >>
>> >> >> Tried VLOOKUP but I can only get it to look for one condition i.e.
>> >> >> date
>> > I
>> >> >> can't do both!
>> >> >>
>> >> >> Any help, pointers greatly appreciated
>> >> >>
>> >> >> Paul T
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>