vlookup question

[JR Winder]

I'm trying to determine the price of item based on the size of that item.
Since I have 244 different sizes and prices, I was thinking the easiest way
to do this would be via vlookup. If there's an easier way, please don'
hesitate to tell me as I'm not at all familure with vlookup.

My spreadsheet looks like this: if you match the height of an item with it's
width, you get it's price. Example 12x12 = 42.00, 42x72=262.00,
78x24=211.00.

Can anyone help?

thanks

W I D T H
12 18 24 30 36 42 48 54 60 66 72 78 84 90 96
12 42 53 64 75 85 97 108 119 129 140 151 163 174 185 196
18 53 65 77 90 101 114 126 138 150 162 174 186 198 211 223
H 24 64 77 91 104 117 130 143 156 169 182 196 211 223 236 249
30 75 90 104 118 132 146 161 175 190 203 217 234 248 261 275
E 36 85 101 117 132 148 163 179 193 209 225 239 257 272 286 302
42 97 114 130 146 163 180 196 213 229 246 262 279 296 312 329
I 48 108 126 143 161 179 196 217 235 252 270 288 306 324 341 359
54 119 138 156 175 193 213 235 253 272 291 310 329 348 367 385
G 60 129 150 169 190 209 229 252 272 293 313 333 354 372 392 412
66 140 162 182 203 225 246 270 291 313 334 355 376 396 417 439
H 72 151 174 196 217 239 262 288 310 333 355 378 399 421 444 466
78 163 186 211 234 257 279 306 329 354 376 399 439 455 479 503
T 84 174 198 223 248 272 296 324 348 372 396 421 455 480 505 531
90 185 211 236 261 286 312 341 367 392 417 444 479 505 533 560
96 196 223 249 275 302 329 359 385 412 439 466 503 531 560 588
102 203 234 262 291 322 350 381 410 439 469 499 527 557 587 615
108 214 246 275 306 337 368 399 439 459 501 521 553 583 613 645

 

[Niek Otten]

Suppose your table is in A118
78 is in A20
24 is in A21

Then this is your formula:

=INDEX(B218,MATCH(A21,A2:A18),MATCH(A20,B11))

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Toppers]

You could use the following:

Place widths in colums B1 to N1 (or finishing column) and name as Width
Place Heights in Rows a2 to a17 (or finishing row) and name as Height
place costs in B2:N17 and name Costs

To find cost:

=index(Costs,match(H,Height,0),match(W,Width,0)) where H and W are Height
and Widths respectively.

=index(Costs,match(12,Height,0),match(18,Width,0)) will find cost of 18 x 12


This assumes W and H are valid! Add error check if required.

HTH

 

[Dave Peterson]

Debra Dalgleish has some nice instructions for using =index(match()) at:
http://www.contextures.com/xlFunctions03.html

And when you're ready to learn more about =vlookup(), Debra has more
instructions at:

http://www.contextures.com/xlFunctions02.html

 

[Glenn Ray]

Assuming you're looking only for an exact match, the previously submitted
solutions (using INDEX and MATCH) will work.

However, your sizing chart looks similar to those used for window treatment
pricing (ex. blinds). In that case, you may need to price up to the next
size in case someone orders a non-standard size. For example, in your price
array, an object 54w by 72h would have a price of 310. But if they wanted
something 58w by 72h, the price should be for a 60w by 72h item, right?

If that assumption is true, you'd have to make two major changes:

1) Invert your array, so that heights descend in value (top to bottom) and
widths descend in value (left to right). Adjust your prices accordingly.

2) Using "toppers" example, label your height values "HeightArray"; your
width values "WidthArray" and your actual prices "PriceArray". If your input
cells are B1 for Height and B2 for Width, you'd change your price lookup
formula to like this:
=INDEX(PriceArray,MATCH(B1,HeightArray,-1),MATCH(B2,WidthArray,-1))

Note the "-1" condition; it looks for the smallest value greater than or
equal to your input variable.

-Glenn Ray

 

[David]

Hi JR.
I took a completely differant approach and thought you might like to into
what you are looking for ie a Height and Width and have the Price returned.
Sub Macro1()
Dim Message, Title, Default, MyValue
Message = "Please enter a width to find"
Title = "Width"
Default = "12"
MyValue = InputBox(Message, Title, Default)
Range("B2:Q2").Select
Selection.Find(What:=(MyValue), After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Col = ActiveCell.Column
Dim Message2, Title2, Default2, MyValue2
Message2 = "Please enter a height to find"
Title2 = "Height"
Default2 = "12"
MyValue2 = InputBox(Message2, Title2, Default2)
Range("B2:B19").Select
Selection.Find(What:=(MyValue2), After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Rw = ActiveCell.Row
Price = Cells(Rw, Col).Value
Msg = "Price is: " & Price
Response = MsgBox(Msg)
End Sub

The table ends up in B2 through Q19. Did this by copying out your data and
doing a Data/Text to columns, but then moving a couple of things that did not
line up. But this allows the user to input two numbers Width and Height, find
column and row, then finds the intersecting value.

Thanks,

 

[T Kirtley]

JR

Combining the Index() & Match() functions as described above works well and
is probably the most common solution, but an interesting alternative would be
to use named ranges to identify each row and column in the price data and
then to simply refer the desired row/column intersection using Excel's
"intersection operator" (a fancy name for a space character). This is simpler
than it may sound at first:

First you would first need to change your column and row headings to unique
values for rows and columns, such as W12, W18... for the widths, and H12,
H18... for the heights, and then create named ranges for each row and column
in the data range. You can make quick work of this by using the
Insert/Name/Create menu option (with both "Top Column" and "Left Column"
selected) to automatically create named ranges for each row and column of
price data. Once that is done you can use a simple and intuitive formula such
as "=H12 W12" to return the value of 42 which is located the cell at the
intersection of the "H12" Row and the "W12" column.

Hope that helps,

TK

 

[JR Winder]

Fantastic observation Glenn- This price is for windows. I was getting
ready to reply to the others thanking them for their suggestions and
then asking them "what if I have a measurment" that isen't exactly
divisable by 6". Your respons was exactly what i was looking for.

Thanks a bunch

JR



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[JR Winder]

When you say "label your height values "HeightArray"; your
width values "WidthArray" and your actual prices "PriceArray" how
exactly do I do that?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[T Kirtley]

Oops, I just looked at my example row and column names and realized that they
would be a problem since they look like cell references. Everything will work
if row and column headers are structured differently, such as "H_12" H_18"...
or "Height_12", Height_18", etc... As such, a formula like "=H_12 W_12" will
return the proper value.

In any case this approach will not allow the return of the next higher value
as Glenn's approach does, so this will probably be a moot point.

 

[Tom Ogilvy]

Select the range of cells and do
Insert =>Name=>Define

The "refers to" box should be defaulted to the selected cells

in the name box put in HeightArray and click ADD.

alternately, with the area selected, go to the name box and enter
HeightArray <Enter key>