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

1. ## 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

2. ## 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))

3. ## 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. ## 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. ## 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. ## 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.
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. ## 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. ## 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. ## 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. ## 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

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

#### 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