Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup question
Suppose your table is in A1:P18
78 is in A20 24 is in A21 Then this is your formula: =INDEX(B2:P18,MATCH(A21,A2:A18),MATCH(A20,B1:P1)) -- Kind Regards, Niek Otten Microsoft MVP - Excel "JR Winder" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup question
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 "JR Winder" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup question
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 JR Winder wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup question
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 "JR Winder" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup question
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, "JR Winder" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup question
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" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup question
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! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup question
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! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup question
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. "T Kirtley" wrote: 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" wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup question
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 -- Regards, Tom Ogilvy "JR Winder" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup question | Excel Worksheet Functions | |||
VLOOKUP Question | Excel Worksheet Functions | |||
vlookup vba question. | Excel Discussion (Misc queries) | |||
Vlookup question please | Excel Worksheet Functions | |||
vlookup question please | Excel Worksheet Functions |