# Simpler Lookup?

#### Janet_B

##### Board Regular
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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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

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.

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.

Glenn,

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

Cheers Janet.

Glenn & Andrew,

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

Much appreciated.
Cheers, Janet

Replies
1
Views
173
Replies
2
Views
387
Replies
1
Views
151
Replies
7
Views
125
Replies
5
Views
304

1,203,462
Messages
6,055,563
Members
444,799
Latest member
CraigCrowhurst

### 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.

### Which adblocker are you using?

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

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