Looking up values from a table or array.

Hi there guys,

I’m new to this forum and I was wondering if someone out there could help me out. I’ve got many Crane lifting tables and I want to automate a process of selecting the configuration required for my lifts.

The attached is a table that list the maximum weight a crane can lift at a specific radius from the centre of crane (the first column) at a specified boom length (second row). Typically, you want the largest radius and longest boom in an unrestricted situation. I’ve set it up with condition formatting so I can see at a glance what configurations work. I put the weight of my load in cell Q3 and all the configurations that work are highlighted green.

What I want to do is return the values of radius and boom that work for the largest possible radius. ie I wand to put a random weight in Q3 and have excel spit out the co responding value in column 1 and row 2 for the largest possible value in column 1. I don’t really know how to do this. I’ve tried =Vlookup and =choose and they don’t seem to do what I want.

I was wondering if anyone could point me in the right direction with what function to use.

Regards,
Drew.

Thanks Dave, I'm not sure what happened to my reply so I 'll try it again. I think I might have accidently sent you a PM or something.

It’s very close, the last thing I cant figure out is how to return the boom length associated with that radius. So for the weight in the attachment 99.5T, your formula returns 16, which is correct. I now need to return the boom length for that radius which would be 24m.

Does that make sense?

Drew.

Thanks for your help Dave. Much appreciated.