Simpler Lookup?

[Janet_B]

Hi,

I've been able to do this myself but over many columns!!!

I was wondering if there is a simpler way please.

Here is an example:

SW3.8.xls
	K	L	M	N
8	Number	Value
9	1	1000
10	2	2000
11	3	3000
12	2	4000
13	1	5000
14	4	6000
15	3	7000
16
17	Number	Value1	Value2
18	1	1000	5000
19	2	2000	4000
20	3	3000	7000
21	4	6000
For O-C Eq

In K1:K15 I have numbers I would like to use to lookup the "values" in column L9:L15. There are two of each number in K1:K15 starting from 1.

From row 18 to 21 I would like for example (the first line) "1" in K18 to lookup and get the first "1" in K9:K15 and place that value 1000 in L18 and the second occurence of "1" which is 5000 into M18.

Bit of a puzzle! Hope I didn't confuse too much.

Cheers Janet

 

[GlennUK]

You need an extra column of formulae

Hi Janet,

I've added some new formulae in cells M9:M15, which count how many occurences of the number in column K have happened so far ( counting downwards ).

Anyway, here's the example:

Book1
	K	L	M	N
8	Number	Value	Count
9	1	1000	1
10	2	2000	1
11	3	3000	1
12	2	4000	2
13	1	5000	2
14	4	6000	1
15	3	7000	2
16
17	Number	Value
18	1	1000	5000
19	2	2000	4000
20	3	3000	7000
21	4	6000	0
22
23
Sheet1

 

[GlennUK]

I'm not sure if you'll be able to see to the formula in the lower half, so here's an example:

=SUMPRODUCT(($K$9:$K$15=$K18)*($M$9:$M$15=1)*($L$9:$L$15))

from L18, which you can copy down. Copy across and change the "=1" to "=2" for the next column of formulae.

 

[Andrew Poulsom]

In L18 enter:

=VLOOKUP(K18,$K$9:$L$15,2,FALSE)

In M18 enter:

=VLOOKUP(K18,INDEX($K$9:$K$15,MATCH(K18,$K$9:$K$15,FALSE)+1):$L$15,2,FALSE)

and copy both formulas down.

 

[Janet_B]

Glenn,

Thankyou very much, I'll test it in my main spreadsheet.

Cheers Janet.

 

[Janet_B]

Glenn & Andrew,

Nice! You've have just removed a dozen columns of data!

Much appreciated.
Cheers, Janet