+ Reply to Thread
Results 1 to 2 of 2

VLOOKUP or Nested ifs

  1. #1
    Registered User
    Join Date
    05-05-2016
    Location
    Melbourne
    MS-Off Ver
    Excel 2016
    Posts
    31

    VLOOKUP or Nested ifs

    Hello,

    I just have two (2) queries about nested if, Vlookup, Count or Sum if formulas

    1 60% of cost/quote (Column F)
    2 80% of cost/quote (Column F)
    3 Based on ADA schedule (each) or 70% of the cost (whichever is less) Column G & F
    4 Based on ADA schedule (each) or 75% of the cost (whichever is less) Column G & F
    5 Based on ADA schedule Column G
    6 Based on ADA schedule ($98.00 each) or 75% of the cost (whichever is less) Column G & F
    - Column E is the number from 1-6 (as above)
    - The results output into column H


    The current formula:
    =IF(E3=1,F3*0.6,IF(E3=2,F3*0.8,IF(AND(E3=3,G3*A3<F3*A3*0.7),G3*A3,F3*A3*0.7))) & I can't work out how to have the result as '0' if there is no number being validated as above
    I am unsure whether to use more nested if statements (which I get a limit error) or try something else. I also can't get the formula to validate anything past number 3 as above so having trouble extending it to include all the numbers 1-6

    The other issue I face is this:

    I have text entered in one (1) cell only, this could be any one of say six different texts
    What's the formula to say if the cell containing text (L2), contains nothing or is blank, then do nothing, otherwise take a total, subtract the results of a a range of numbers in this case H3:H11. i.e K18-(H3:H11) if there is text in L2

    This is to say the family limit is to be reduced by the benefits payable if the product has a family limit applicable (obviously this I would have also only typed a balance in there if it's applicable) So the formula could also be based on K18 being more than zero 0.00

    Thank you
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: VLOOKUP or Nested ifs

    Try

    =IF(E3=1,F3*0.6,IF(E3=2,F3*0.8,IF(E3=3,MIN(G3*A3,F3*A3*0.7),IF(E3=4,MIN(G3*A3,F3*A3*0.75),IF(E3=5,G3,IF(E3=6,MIN(98,F3*A3*0.75),0))))))

    .... not sure If I have all the calculations correct but you should be able to correct.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Nested If, And, Vlookup?
    By TotallyLost in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-19-2016, 09:35 AM
  2. [SOLVED] Nested Vlookup, or Vlookup that looks at two columns, not just one?
    By FirestarZA in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-24-2014, 08:18 AM
  3. Nested If AND with VLookup
    By MrsRobinson in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-11-2013, 08:54 AM
  4. Nested IF's and VLOOKUP's
    By OsingaL in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-14-2011, 09:55 PM
  5. Nested VLOOKUP with IF
    By rganz in forum Excel General
    Replies: 1
    Last Post: 06-15-2011, 08:02 PM
  6. Vlookup across sheets, nested Vlookup possibly?
    By paid2mkgrlspanic in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 06-09-2009, 05:10 PM
  7. Nested If with VLOOKUP's... I think!
    By PeterManner in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1