+ Reply to Thread
Results 1 to 10 of 10

Index & Match for multiple Column and Row to find header...

  1. #1
    Registered User
    Join Date
    05-14-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    11

    Index & Match for multiple Column and Row to find header...

    Hi all, this is my first post and can usually bodge a formula together from posts on this forum but can't get it this time.

    What I am trying to get is the minimum crane name based on three minimum criteria.
    1. In 'C10' I can get the minimum row number.
    2. In 'D10' I can get the minimum column number.
    3. In 'E10' I can cross reference the two to get the maximum lift of that selection.
    4. In 'F10' you can see that that crane cannot lift the load i need so it fails, what it need to do is look to the left of the row until it finds a column with a value greater that the lift.
    5. In 'C14' I can't get that final row/column reference needed in 'F10' to then get the crane name listed in 'K6:T6'

    Hope some of this makes sense and all help is greatly appreciated.
    C
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Index & Match for multiple Column and Row to find header...

    Try this formula

    =INDEX(K$6:T$6, MATCH($C$6, INDEX($K$8:$T$30,MATCH($C$5,$J$8:$J$30,-1),),-1))
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    05-14-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Index & Match for multiple Column and Row to find header...

    Thanks ChemistB,
    That covers 2 of the 3 items but not the minimum boom length in 'D10'
    Thanks again for a very speedy response and hope you can add this bit in for me.
    C

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Index & Match for multiple Column and Row to find header...

    D10 should be

    =INDEX($K$7:$T$7,MATCH(C9,K7:T7,-1))
    Is that what you are looking for? or do you want the boom length associated with "40T City"?

  5. #5
    Registered User
    Join Date
    05-14-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Index & Match for multiple Column and Row to find header...

    ChemistB,
    No sorry, I mean the formula should include items 1, 2 & 3 to get the crane type. Your formula only took into account items 1 & 3.

    Basically once the minimum Boom length is calculated and the lift radius used that crane may not be able to lift that load, therefore move to the left of that row (increasing the crane size and load capacity) to get a crane that can lift the required load or more.

    Sorry if this not clear, but your original formula is so close, it just doesn't incorporate the minimum boom length calculation in cell 'C9' and the formula (possibly incorrect) in cell 'D10'.

    Hope this makes sense and thank you so much for all your time and help.
    C

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Index & Match for multiple Column and Row to find header...

    =INDEX(K$6:T$6, MATCH($C$6, INDEX($K$8:$T$30,MATCH($C$5,$J$8:$J$30,-1),),-1))

    This part INDEX($K$8:$T$30,MATCH($C$5,$J$8:$J$30,-1),) goes down the left side and picks the appropriate row based on your Lift Radius

    The first part takes that minimum lift radius and goes across until it finds the shortest boom length that can still carry your load.
    If that boom length is less than your minimum boom length, then you would need to alter either your minimum boom length, your load capacity or your lift radius.
    Does that make sense? I could set it within an IF statement so that if that equipment doesn't meet your criteria, it tells you. Or you could use this additional formula to pull the boom length on that equipment

    =INDEX(K$7:T$7, MATCH($C$6, INDEX($K$8:$T$30,MATCH($C$5,$J$8:$J$30,-1),),-1))

  7. #7
    Registered User
    Join Date
    05-14-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Index & Match for multiple Column and Row to find header...

    ChemistB,
    I understand what you are saying about the formula but the formula is missing the minimum boom length calculated in Cell 'C9' then referenced in cells 'K7:T7', it needs that check within the formula to stop the row being referenced going to far to the right (smaller crane with a shorter boom).

    Sorry once again and thanks,
    C

  8. #8
    Registered User
    Join Date
    05-14-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Index & Match for multiple Column and Row to find header...

    As my previous post, I've been playing with the formula and it is missing the "MATCH(C9,K7:T7,-1)" part of the function/equation.
    I tried various options and all come up with the #VALUE or #REF result!!!
    I can see ChemistB has they correct approach and it is 90% there, i just need to add this check in also.

    All help greatly appreciated.
    C

  9. #9
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Index & Match for multiple Column and Row to find header...

    Ahhh, the lightbulb goes on. I see what you are saying. This formula will do what you are asking for I believe

    =INDEX(K$6:T$6, MIN(MATCH($C$9,$K$7:$T$7,-1),MATCH($C$6, INDEX($K$8:$T$30,MATCH($C$5,$J$8:$J$30,-1),),-1)))

    That should always give you the proper boom length that is still able to carry your load.

  10. #10
    Registered User
    Join Date
    05-14-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Index & Match for multiple Column and Row to find header...

    ChemistB,
    You are an absolute star, that works 100%.
    Excellent work, even at 7am!!

    Thanks again for all your help and sorry to be a pain.
    C

+ 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. [SOLVED] OFFSET+ INDEX-MATCH to find first non-zero column
    By Ztv in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-04-2014, 01:51 PM
  2. How do i find the max of two values from index match max of a column
    By mohaymin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-09-2013, 02:04 PM
  3. [SOLVED] Index and Match Multiple Header (Rows) Criteria
    By dluhut in forum Excel General
    Replies: 7
    Last Post: 04-12-2012, 06:25 PM
  4. Return Column Header from Index/Match function
    By del24ie in forum Excel General
    Replies: 2
    Last Post: 01-12-2012, 11:57 AM
  5. Index/Match Function to Return column header
    By djmarsh51 in forum Excel General
    Replies: 2
    Last Post: 05-07-2010, 02:10 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