Results 1 to 8 of 8

Thread: Vlookup?

  1. #1
    5 Star Lounger
    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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]

    Steve
    Last edited by sdckapr; 2011-08-31 at 07:17.

  3. #3
    5 Star Lounger
    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.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    5 Star Lounger
    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.
    Attached Files Attached Files

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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

  7. #7
    5 Star Lounger RussB's Avatar
    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 <))>(

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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.
    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
  •