+ Reply to Thread
Results 1 to 8 of 8

Nested IF/OR with VLOOKUP formula

  1. 05-07-2012, 01:52 PM #1
    jakeisbill
    jakeisbill is offline
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Myrtle Beach, South Carolina
    MS-Off Ver
    =IF(Win 7,"Excel 2010","Mac Excel 2011")
    Posts
    104

    Nested IF/OR with VLOOKUP formula

    I have hit a brick wall while trying to write this formula. Attached I have a sheet that would calulate the total weight of a shipment of Probes, our product, I have two tables. The first table is where the employee would enter the Length of the Probe, and the Length of the cable attached. The second table is made of tested and weighed probes all with 10ft cables. I would like to write a formula that compute the weight if there is a longer length of cable attached. I have a list on the sheet of just the cables by weight and length, along with the weight difference. I have a formula in the Weight column using VLOOKUP to table two, I got confused when I tried to write =IF("cable length" =10ft,(VLOOKUP(bla,bla,bla,True)??? thats where I draw a blank. Please and Thank You in Advance.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-07-2012, 01:57 PM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Nested IF/OR with VLOOKUP formula

    Do you mean?

    =IF(B6="","",VLOOKUP(B6,$K$2:$M$59,3,TRUE)/10*C6)

    copied down.

    this will proportion out the weight per FT to the Length in C.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 05-07-2012, 02:02 PM #3
    jakeisbill
    jakeisbill is offline
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Myrtle Beach, South Carolina
    MS-Off Ver
    =IF(Win 7,"Excel 2010","Mac Excel 2011")
    Posts
    104

    Re: Nested IF/OR with VLOOKUP formula

    Were on the right path, but the weight is not so incremental. In Column O-Q I have a weight chart associated witht the cable length.
    Reply With QuoteRegister To Reply
  4. 05-07-2012, 02:11 PM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Nested IF/OR with VLOOKUP formula

    So for 20FT you would add 1.25 lb per foot after the first 10 ft no matter the probe length?
    Reply With QuoteRegister To Reply
  5. 05-07-2012, 02:14 PM #5
    jakeisbill
    jakeisbill is offline
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Myrtle Beach, South Carolina
    MS-Off Ver
    =IF(Win 7,"Excel 2010","Mac Excel 2011")
    Posts
    104

    Re: Nested IF/OR with VLOOKUP formula

    You would add 0.6 to the total for the 20ft cable. no mater the probe length. The weights for the probes we have are all with 10ft cables.
    Last edited by jakeisbill; 05-07-2012 at 02:17 PM. Reason: typo
    Reply With QuoteRegister To Reply
  6. 05-07-2012, 02:16 PM #6
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Nested IF/OR with VLOOKUP formula

    Change the values in column 0 to numerics only (you can format to show FT as you have done elsewhere)... and then use formula:

    =IF(B9="","",VLOOKUP(B9,$K$2:$M$59,3,TRUE)+VLOOKUP(C9,$O$3:$Q$9,3,TRUE))

    copied down.
    Reply With QuoteRegister To Reply
  7. 05-07-2012, 02:25 PM #7
    jakeisbill
    jakeisbill is offline
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Myrtle Beach, South Carolina
    MS-Off Ver
    =IF(Win 7,"Excel 2010","Mac Excel 2011")
    Posts
    104

    Re: Nested IF/OR with VLOOKUP formula

    I like it. that is the second time youve helped me out buddy. I appreciate it. Thank You.
    Reply With QuoteRegister To Reply
  8. 05-07-2012, 02:28 PM #8
    jakeisbill
    jakeisbill is offline
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Myrtle Beach, South Carolina
    MS-Off Ver
    =IF(Win 7,"Excel 2010","Mac Excel 2011")
    Posts
    104

    Re: Nested IF/OR with VLOOKUP formula

    I didn't know about adding a + into an IF formula.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1