Lookup help

[Paul T]

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

 

[Bob Phillips]

=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]

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]

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]

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]

Good stuff Paul. Gooners man?

Bob

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

 

[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