Lookup array

[Lewyg]

The following is a example of part of a spread sheet that I use.

Book2
	A	B	C	D	E
1	DOOR PANEL	777	316
2	Adjustable Shelf	285	539
3	Bottom	568	544
4	Top	568	91
5	Back Panel	780	568
6	Wall Side	780	560
7	Wall Side	780	560
8	Horiz Partition	568	544
9	DRAWER FRONT	598	172
...

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
	A	B	C	D	E
1		100 - 200	201 - 300	301 - 400	401 - 500
2	100 - 200	12.10	23.00	40.20	51.00
3	201 - 300	13.50	25.60	56.80	53.95
4	301 - 400	15.60	30.10	64.70	56.70
5	401 - 500	18.30	34.00	68.30	87.00
6	501 - 600	25.70	38.90	69.20	88.70
7	601 - 700	30.40	39.80	71.40	91.00
...

 

[Aladin Akyurek]

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?

 

[Lewyg]

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.

 

[Aladin Akyurek]

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
	A	B	C	D	E
1		0	201	301	401
2	100	12.1	23	40.2	51
3	201	13.5	25.6	56.8	53.95
4	301	15.6	30.1	64.7	56.7
5	401	18.3	34	68.3	87
6	501	25.7	38.9	69.2	88.7
7	601	30.4	39.8	71.4	91
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;
"Adjustable Shelf",285,539;
"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
	A	B	C	D
1	DOORPANEL	777	316	71.4
2	AdjustableShelf	285	539	53.95
3	Bottom	568	544	88.7
4	Top	568	91	25.7
5	BackPanel	780	568	91
6	WallSide	780	560	91
7	WallSide	780	560	91
8	HorizPartition	568	544	88.7
9	DRAWERFRONT	598	172	25.7
Sheet1