1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

nested formula limit

Discussion in 'Ask an Excel Question' started by Rhon Alvarez, Jun 10, 2018.

  1. Rhon Alvarez

    Rhon Alvarez Member

    Messages:
    51
    Hi Team,

    I need help with this file (Reporting Requirements_v1), to extract data from another file (New ABS -06.01.2018).
    I tried nesting the VLOOKUP however nesting limit won't allow me for there are 10 columns that I need to look the data from. Is there any formula, other than nested VLOOKUP, I can use to be able to extract necessary data.
    I've attached both files for your reference, however, I opted to delete other sheets on New ABS file since it won't allow me to upload because of its size.

    Thanks in advance.

    Attached Files:

  2. AliGW

    AliGW Active Member

    Messages:
    272
    Which version of Excel are you using?
  3. Rhon Alvarez

    Rhon Alvarez Member

    Messages:
    51
    excel 2013. our office doesn't have any plans of upgrading yet to a higher version.

    thanks.
  4. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    427
    The pattern of nested IFs you show can be replaced by CHOOSE (since IFS is not available to you). The first step is to use MATCH to find the index corresponding to the relevant term in $B1:$B10. That value is used as the first parameter of the CHOOSE in order to select the appropriate VLOOKUP.

    In your particular case, the VLOOKUP range appears to be the same in all cases (give or take some additional columns on the right) so the entire formula can be reduced to a single VLOOKUP (or INDEX/MATCH). All that changes is the column number to return and that could be looked up knowing the result of the first MATCH. e.g.
    = INDEX( {14;16;20;22;26;28;38}, MATCH($A$30,$B$1:$B$10,0) )
    Thomas Kuriakose likes this.
  5. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    In case your problem has not yet been resolved , please upload the second workbook with the worksheet named ABS Template in it.

    At present the second file which has been uploaded does not have this worksheet.

    Narayan
  6. Rhon Alvarez

    Rhon Alvarez Member

    Messages:
    51
    Hi Narayan,

    please find attached file as requested. I uploaded the same file but from another date since we have recently stop the use of some sheets included on the file. all i need was to extract data from the "IEX Dump ND 102 MU 2101" sheet which consists of different columns and rows and limit of vlookup is not allowing me to do such.

    thanks in advance.

    Attached Files:

  7. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    Can you please explain your requirement , in more detail , using Excel references ?

    1. In which worksheet tab , in which cell does the formula need to be entered ?

    2. Which other cells does it have to be copied to ?

    3. What should the formula do , which is the input cells it should use to derive the output ?

    4. Can you manually enter the required output in one or two cells so that what ever formula is suggested can be verified for correctness using these manully entered outputs ?

    Narayan
  8. Rhon Alvarez

    Rhon Alvarez Member

    Messages:
    51
    Hi Narayan,

    The formula needs to be in the "FTE_OCC" worksheet (d32 to I32 & d39 to I39) on the Reporting requirements_v1 file.

    for "IBOR HR Protected..." the value should come from "New Abs.." file "IEX DUMP ND 102 MU 2101" sheet depending on the lookup value from "A30"

    for illustration, on reporting requirements_v1:
    if a30 = TMT prod
    (rep.req_v1) d32 should be equal to (new abs.) AD125 ; e32 = ad126 ; f32 = ad127 ; g32 = ad127 and so on (transposition)
    if a30 = TMT OJT
    (rep.req_v1) d32 should be equal to (new abs.) Af125 ; e32 = af126 ; f32 = af127 ; g32 = af127 and so on (transposition)
    if a30 = CORE prod
    (rep.req_v1) d32 should be equal to (new abs.) Aj125 ; e32 = aj126 ; f32 = aj127 ; g32 = aj127 and so on (transposition)
    and so on...

    this should apply as well with IBOR PLANNED portion (d39 to i39) to be linked to rows 102 to 109 (same columns)

    apologies for i couldn't provide inputs since the file is too large and I need to delete most of the sheets included. You may manually input data for checking purposes.

    hope this could help in explaining further and thanks in advance. :) :) :)
  9. Rhon Alvarez

    Rhon Alvarez Member

    Messages:
    51
    just a screenshot..

    Attached Files:

  10. AliGW

    AliGW Active Member

    Messages:
    272
    Upload the file, not a picture of it.
  11. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    See if these formulae work.

    I have defined 3 named ranges , which you will have to define in your work file.

    Narayan

    Attached Files:

    Thomas Kuriakose likes this.

Share This Page