array and lookup problem
 

I have a spreadsheet as follows:

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

...............................................low est........corresponding
1...'x'...'y'.......AR..............AR........'y'. ...............'x'

2...3....2.........1................1.........2... ..............* (see
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 varied
numbers of competitors in each race) stacked vertically which I would
like to analyse separately. So I have manually added column 3 - which
allocates a separate array number to each separate race event and then
I have constructed a summary area (columns F, G and H above) that
summarises each race on consecutive rows.

I have written a formula for column F that automatically pulls out the
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 there
an easier way to do it? I am just about exhausted of attempts and
ideas...any help would be gratefully received...:)


--
jaimetimbrell
------------------------------------------------------------------------
jaimetimbrell's Profile: http://www.excelforum.com/member.php...o&userid=26162
View this thread: http://www.excelforum.com/showthread...hreadid=506313

array and lookup problem
 

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

array and lookup problem
 

Dear Kostis,

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

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

Best Regards,
Jaime.


--
jaimetimbrell
------------------------------------------------------------------------
jaimetimbrell's Profile: http://www.excelforum.com/member.php...o&userid=26162
View this thread: http://www.excelforum.com/showthread...hreadid=506313

array and lookup problem
 

Jaime

Glad it worked.

Regards,
Kostis