# Lookup array

#### Lewyg

##### New Member
The following is a example of part of a spread sheet that I use.
Book2
ABCDE
1DOOR PANEL777316
3Bottom568544
4Top56891
5Back Panel780568
6Wall Side780560
7Wall Side780560
8Horiz Partition568544
9DRAWER FRONT598172
...

I want look for those cells that contain "Door Panel" or "drawer front" then using the two sizes in the columns beside look up the price in the following table.
PRICE.xls
ABCDE
1100 - 200201 - 300301 - 400401 - 500
2100 - 20012.1023.0040.2051.00
3201 - 30013.5025.6056.8053.95
4301 - 40015.6030.1064.7056.70
5401 - 50018.3034.0068.3087.00
6501 - 60025.7038.9069.2088.70
7601 - 70030.4039.8071.4091.00
...

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
What happens when

(a) the first size under 100;

(b) the first size above 700;

(c) the second size under 100;

(d) the second size above 500?

when the size is under 100 we use the smallest. I surpose the first row and column should read 0 - 200, the example I used was only part of the list, we take it right up to 2700 which is as big as the come.

On 2002-08-29 03:29, Lewyg wrote:
when the size is under 100 we use the smallest. I surpose the first row and column should read 0 - 200, the example I used was only part of the list, we take it right up to 2700 which is as big as the come.

Organize your lookup table for prices as shown in the figure...
aaPriceLookup LewyG.xls
ABCDE
10201301401
210012.12340.251
320113.525.656.853.95
430115.630.164.756.7
540118.33468.387
650125.738.969.288.7
760130.439.871.491
Sheet2

( 1.) Select all of the cells of this table, from A1 to E7.
( 2.) Go to the Name Box on the Formula Bar, type PriceTable, and hit enter.

Let A1:C9 house the items with associated sizes for which you want to determine the prices:

{"DOOR PANEL",777,316;
"Bottom",568,544;
"Top",568,91;
"Back Panel",780,568;
"Wall Side",780,560;
"Wall Side",780,560;
"Horiz Partition",568,544;
"DRAWER FRONT",598,172}

In D1 enter & copy down:

=VLOOKUP(B1,PriceTable,MATCH(C1,INDEX(PriceTable,1,0)))

See the figure...
aaPriceLookup LewyG.xls
ABCD
1DOORPANEL77731671.4
3Bottom56854488.7
4Top5689125.7
5BackPanel78056891
6WallSide78056091
7WallSide78056091
8HorizPartition56854488.7
9DRAWERFRONT59817225.7
Sheet1

Replies
9
Views
463
Replies
10
Views
712
Replies
9
Views
393
Replies
7
Views
136
Replies
0
Views
405

1,221,054
Messages
6,157,646
Members
451,428
Latest member
Cypt2o

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