Lookup array

Lewyg

New Member
Joined
Aug 27, 2002
Messages
2
The following is a example of part of a spread sheet that I use.
Book2
ABCDE
1DOOR PANEL777316
2Adjustable Shelf285539
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

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Aug 27, 2002
Messages
2
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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;
"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
ABCD
1DOORPANEL77731671.4
2AdjustableShelf28553953.95
3Bottom56854488.7
4Top5689125.7
5BackPanel78056891
6WallSide78056091
7WallSide78056091
8HorizPartition56854488.7
9DRAWERFRONT59817225.7
Sheet1
 

Forum statistics

Threads
1,148,524
Messages
5,747,183
Members
424,068
Latest member
Salim khamis

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