+ Reply to Thread
Results 1 to 8 of 8

Nested formula required for two cell inputs and a single cell output

  1. #1
    Registered User
    Join Date
    04-11-2012
    Location
    Nottingham
    MS-Off Ver
    Excel 2010
    Posts
    11

    Nested formula required for two cell inputs and a single cell output

    Hi!

    Looking for some help with a nested formula (or an alternative method to achieve the same task) for following:

    I have two blank cells - C12 and C13. Depending on the inputs of these cells, I require a set output in cell C15.

    Result required in C15 to be determined from inputs as below:

    C12 In_______C13 In______C15 Out
    ___1____________2________Kentec XT
    ___1____________4___Kentec XT+ 1 Area 4 Zone
    ___1____________6___Kentec XT+ 1 Area 8 Zone
    ___2____________2___Kentec XT+ 2 Area 2 Zone
    ___2____________4___Kentec XT+ 2 Area 4 Zone
    ___2____________6___Kentec XT+ 2 Area 8 Zone
    ___3____________2___Kentec XT+ 3 Area 2 Zone
    ___3____________4___Kentec XT+ 3 Area 4 Zone
    ___3____________6___Kentec XT+ 3 Area 8 Zone
    ___4____________2___Kentec XT+ 4 Area 2 Zone
    ___4____________4___Kentec XT+ 4 Area 4 Zone
    ___4____________6___Kentec XT+ 4 Area 8 Zone

    Where the conditions above are not met (FALSE), C15 should report "Please enter correct information above".

    Have tried nesting IF AND formula but a little beyond my level of EXCEL!!

    Any help would be hugely appreciated.
    Last edited by titch2k6; 03-10-2013 at 12:18 PM. Reason: Change of title

  2. #2
    Forum Contributor
    Join Date
    09-27-2012
    Location
    Florida
    MS-Off Ver
    Excel XP to 2013
    Posts
    342

    Re: Help required for required formula task

    Can you use data validation lists to restrict the choices in C12 and C13 to only those on your list? If so you could concatenate the two cell values and use a vlookup to get your output.

  3. #3
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Help required for required formula task

    See my attached sheet and see it as an example of a solution you can use. Note, the formula used is an array formula that needs to be applied with Ctrl + Shift + Enter, instead of just Enter.

    =IFERROR(INDEX($J$1:$J$12,MATCH(1,($H$1:$H$12=$C$12)*($I$1:$I$12=$C$13),0)),"Please enter correct information above.")

    - Moo
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,601

    Re: Help required for required formula task

    This seems to work. Added some datavalidation but it handles error fine without it.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  5. #5
    Registered User
    Join Date
    04-11-2012
    Location
    Nottingham
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Help required for required formula task

    Quote Originally Posted by Moo the Dog View Post
    See my attached sheet and see it as an example of a solution you can use. Note, the formula used is an array formula that needs to be applied with Ctrl + Shift + Enter, instead of just Enter.

    =IFERROR(INDEX($J$1:$J$12,MATCH(1,($H$1:$H$12=$C$12)*($I$1:$I$12=$C$13),0)),"Please enter correct information above.")

    - Moo
    Many thanks for this Moo - Worked a treat.

    Can this be extended to add the following function?

    I want a reference for each panel type listed in cell C15 to be placed in cell C16 (basic numeric from 1 to 12 dependent on Panel model given in C15) which then I can reference through a nested IF formula (which I can work out) to give me a unit price and part number within specified cells.

  6. #6
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Help required for required formula task

    titch2k6,

    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.

    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  7. #7
    Registered User
    Join Date
    04-11-2012
    Location
    Nottingham
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Help required for required formula task

    Hi guys and gals and thanks for all the input. Solved the above question by using a VLOOKUP formula.

  8. #8
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Help required for required formula task

    Thanks for the rep. Sorry I didn't get back sooner, was afk most of the day.

    - Moo

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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