Simpler Lookup?

Janet_B

Board Regular
Joined
Dec 6, 2004
Messages
72
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
KLMN
8NumberValue
911000
1022000
1133000
1224000
1315000
1446000
1537000
16
17NumberValue1Value2
18110005000
19220004000
20330007000
2146000
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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
KLMN
8NumberValueCount
9110001
10220001
11330001
12240002
13150002
14460001
15370002
16
17NumberValue
18110005000
19220004000
20330007000
21460000
22
23
Sheet1
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
I'm not sure if you'll be able to see to the formula in the lower half, so here's an example:
Code:
=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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Dec 6, 2004
Messages
72
Glenn,

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

Cheers Janet.
 

Janet_B

Board Regular
Joined
Dec 6, 2004
Messages
72
Glenn & Andrew,

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

Much appreciated. :biggrin:
Cheers, Janet
 

Watch MrExcel Video

Forum statistics

Threads
1,129,803
Messages
5,638,446
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top