MATCH or LOOKUP Help 1

[osx99]

I understand the concepts of match and vlookup and can get it to work using a single column for the range. However, I have a particular problem that I'm stumped with

if I have data arranged in columns
A
test1
test2
test3
test4

Then the formula =Match("test3",A1:A4,0) would return 3

However, I have data arranged as
A B
test1 test2
test3 test4

Is there a way to return a match based on more than a single column range? In this case I want result = 2

Eg = Match("test3",A1:B2,0) returns #N/A

Any help with this is greatly appreciated

Os

 

[SkipVought]

Hi,

However, I have data arranged as[tt]
A B
test1 test2
test3 test4[/tt]

WHY???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[osx99]

It's football results data. Column A is home team, Column B is away team but I've coded the home and away to reveal number of matches played so far. I'm trying to lookup data based on last 6 games

I'm stuck with the way data is prented to me and would rather code a neat lookp than re-process all data

 

[SkipVought]

[tt]
Team Loc
test1 H
test2 A
test3 H
test4 A
[/tt]
is a better structure.

you probably need a column for GameDate, too, if not more.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

The structure is mor like this...
[tt]
Game Team Loc
1 test1 H
1 test2 A
2 test3 H
2 test4 A
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[osx99]

eg

A B C D E F
Aston Villa1 Bolton1 Aston Villa Bolton 2 2
.
.
.
Man United2 Aston Villa2 Man United Aston Villa 1 0

Etc

 

[SkipVought]

Please LOOK at what you posted and then explain HOW one can understand WHAT it means?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[osx99]

I understand what your saying and was trying to find a smarter way where I don't have to reprocess 1000's of lines of data

I hoped for a solution where I could just dump exported data without the need for pre-processing

Maybe VBA is way to go

 

[SkipVought]

You could REPORT the data "horizontally", but the SOURCE ought to be in a NORMALIZED table, IMHO.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Use the Find method in VBA, if you so choose.

Or set up 2 MATCH functions. If you get an NA one then get the result from the other. That may work for you.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[osx99]

Or set up 2 MATCH functions. If you get an NA one then get the result from the other

Doh - why didn't I think of that!! Easy!

Thanks!