array and lookup problem

[jaimetimbrell]

I have a spreadsheet as follows:

.....A....B........C.....D..E.......F........G................H

...............................................lowest........corresponding
1...'x'...'y'.......AR..............AR........'y'................'x'

2...3....2.........1................1.........2.................* (se
below for formula)

3...2....3.........1................2.........3.................**

4...4....5.........1................3........etc..............***

5...5....4.........1

6...1....6.........1

7

8....'x'.....'y'....AR

9.....2......5......2

10....3.....6......2

11....1.....3......2

12

13....'x'...'y'....AR

14.....1....7......3

15.....3....8......3

16.....4....1......3

etc...etc...etc...etc

I have a spreadsheet with about 300 separate race events (with varie
numbers of competitors in each race) stacked vertically which I woul
like to analyse separately. So I have manually added column 3 - whic
allocates a separate array number to each separate race event and the
I have constructed a summary area (columns F, G and H above) tha
summarises each race on consecutive rows.

I have written a formula for column F that automatically pulls out th
lowest 'y' {=(MIN(IF($C$2:$C$6000=ROWS($A$2:A2),$B$2:$B$6000))))}
which works

Then I have tried to write formulae that pull out the corresponding 'x
value (below), from looking up the lowest 'y' value:

*{=LOOKUP(G2,(IF($C$2:$C$6000=ROWS($A$2:$A2),$B$2:$B$6000)),$A$2:$A$6000)}

**{=LOOKUP(G3,(IF($C$2:$C$6000=ROWS($A$2:$A3),$B$2:$B$6000)),$A$2:$A$6000)}

***{=LOOKUP(G4,(IF($C$2:$C$6000=ROWS($A$2:$A4),$B$2:$B$6000)),$A$2:$A$6000)}

etc

But these dont work and I cannot see why - can anyone else? Or is ther
an easier way to do it? I am just about exhausted of attempts an
ideas...any help would be gratefully received...:

 

[vezerid]

Jaimetimbrell,

Although you do not specify what you mean they do not work, I suspect
you are getting an error because LOOKUP expects an actual range
reference and not a computed array, as you are supplying.

Try the combination INDEX($A$2:$A$6000, MATCH(G2,
IF($C$2:$C$6000=ROWS($A$2:$A2),$B$2:$B$6000)))

Does this help?
Kostis Vezerides

 

[jaimetimbrell]

Dear Kostis,

I think you are right, but I just didnt understand why it wasnt workin
- I have tried your solution and it has led to the correct equation.
just wasnt usin lookup and arrays in the right way together.

I am very grateful to you for your interest in my problem and takin
time to provide me with an alternative solution.

Best Regards,
Jaime

 

[vezerid]

Jaime

Glad it worked.

Regards,
Kostis