Results 1 to 8 of 8
Thread: Vlookup?

20110831, 05:28 #1
 Join Date
 May 2003
 Location
 Sheffield, Yorkshire
 Posts
 951
 Thanks
 1
 Thanked 0 Times in 0 Posts
Vlookup?
I have a spreadsheet which I enter data based on three criteria. How long an operation takes, and how many there are. The third criteria comes in when deciding where the 'how long' comes from. We have another spreadsheet, with column headings Small, Medium, Large, with times (how long) underneath. Basically, I want one spreadsheet to lookup a value in another, and multiply the result by the quantity to be made. What I'm not sure of is, do I use VLookup? Or Index? And where can I learn the difference, and how to use them.

20110831, 06:09 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Can you post an example of the setup and clarify exactly what you need?
The basic difference is in the index you have a table/array and you explicitly give it the row and column index value. with vLookup you give it value from the first column and a column index and it looks up the row index from the column value (a vertical lookup). With HLookup you give it a column value and a row index and it looks up the column index from the column value (a horizontal lookup).
But an idex is generic in that you can use it combined with MATCH to determine the column and/or the row index values if desired.
It can also get a little more complex if you want to interpolate within a table if you are not after exact matches but have continuous values and you want a value between rows and/or between columns. [Often a "model equation" can work better than a lookup in this case if you can find one accurate enough]
SteveLast edited by sdckapr; 20110831 at 06:17.

20110831, 07:22 #3
 Join Date
 May 2003
 Location
 Sheffield, Yorkshire
 Posts
 951
 Thanks
 1
 Thanked 0 Times in 0 Posts
Table 1.jpg Table 2.jpg
The green highlighted area is the area that I complete at the moment. Some ops are set, like op1 for Small Commercial, will always be 5. Others are multiplied by the time in the second image. So for Op4, which is 2.7, this is multiplied by the quantity (this is in a cell on the first table), which in this case is 112. This gives Op4, 302 minutes. My plan is to put a cell on the first table, stating whether the part is Small Commercial, Medium Commercial, or Large Commercial. This would determine which column the formula uses to find the correct cell, but I don't know how to achieve this. Hope all this has made sense.

20110831, 08:55 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
It would be better with workbook, not a picture of a workbook...
Steve

20110831, 09:07 #5
 Join Date
 May 2003
 Location
 Sheffield, Yorkshire
 Posts
 951
 Thanks
 1
 Thanked 0 Times in 0 Posts
Here you are. I've stripped the unnecessary bits.

20110831, 12:38 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
You can use something in A3 like
=VLOOKUP(B3,Sheet2!$A$3:$D$30,MATCH("Small commercial ",Sheet2!$A$1:$D$1,0),0)
and in A6 something like:
=VLOOKUP(B6,Sheet2!$A$3:$D$30,MATCH("Small commercial ",Sheet2!$A$1:$D$1,0),0)*$G$1
to multiply by the number /mould
Instead of "Small commercial ", entered explicitly, you can refer to a cell reference. I don't see a way that it can decide which multiply by the number/mould and which do not...
Steve

20110831, 14:53 #7
 Join Date
 Dec 2009
 Location
 Grand Rapids, Michigan
 Posts
 803
 Thanks
 10
 Thanked 50 Times in 49 Posts
Remember the =VLOOKUP needs the target range to be sorted by the target column with no blank cells.
Do you "Believe"? Do you vote? Please Read:
LEARN something today so you can TEACH something tomorrow.
DETAIL in your question promotes DETAIL in my answer.
Dominus Vobiscum <))>(

20110831, 15:03 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Remember the =VLOOKUP needs the target range to be sorted by the target column with no blank cells.
Steve