1. ## 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.

2. 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]

Steve

3. 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.

4. It would be better with workbook, not a picture of a workbook...

Steve

5. Here you are. I've stripped the unnecessary bits.

6. 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

7. Remember the =VLOOKUP needs the target range to be sorted by the target column with no blank cells.

8. Remember the =VLOOKUP needs the target range to be sorted by the target column with no blank cells.
Not if you are looking for an exact match, which seems to be the case here...

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•