Lookup or index on multiple parameters and pull table heading for matching values

Hi,Excel Table.PNG

I've run into a lot of difficulty trying to extract a column heading from a data table based on two parameters in a table. I've attached a snapshot of my dilemma in my excel worksheet. I start with a given value for height (the first parameter) and a given demand force (the second parameter) to enter and look for in my table. I need my height to find the next largest height in the left column based on increments of .5 (if my heights is 9.3 ft it should find 9.5 ft, the next greatest height in .5 increments). After finding the row associated with the design height (1st parameter) I need to find the first largest column capacity in that row for my given force demand (2nd parameter). After I find that next largest capacity in that table I need to output the size of the design column for that demand, which is the header of the column. The only output I need is the column size at the header.

For the first requirement of matching the height I have tried roundup and match but cannot figure out how to get it in .5 increments. I have also tried match, index and vlookup functions to get the second parameter and column heading but am getting nowhere.

Please help.

As far as finding the row number, try the formula:

Please Login or Register  to view this content.

This assumes that your values for height are in A2:A22 and that the input for the first parameter comes from I9. I need understand how the 2nd parameter, force demand, is input, so I can help beyond that. I think you could get some faster, better results if you could post the file from which the snap shot was made.

Based on my understanding of the description and the table I believe that this formula will do what you want:

Please Login or Register  to view this content.

This assumes the input for the 1st parameter is from D23 and the input for the 2nd parameter is from F23. I did a "mock up" of what the table might look like so that you can see if it matches the results you expect from the actual data table.

Post Axial Capacity.xlsx

Hope this helps.

Thank you JeteMC and quekbc! Your knowledge and willingness to help is amazing!

I've attached an example excel file as JeteMC recommended. Both of you have great solutions for a piece of the puzzle, but I can't formulate a combination of the two to accomplish the task, your skills are a little over my head.

Jete MC, do you know how to modify your equation so the row selected based on parameter 1 can use parameter 2 to find the next greatest value in that row and output the header?

Quekbc, With your solution I need to match the first parameter with the the next highest value in .5 increments for Column B. For some reason it's not pulling out the the post size for a capacity greater than the demand, it's pulling out a size that has a capacity less than the demand, but your pipe diameter spreadsheet was accomplishing this. I removed the offset equation because I don;t think it is needed in my example.

Thanks Glenn! The formula works for parameter two if I have the posts capacities in ascending order form left to right which I can easily change, 3-2x6 and 6x6 are currently not in ascending order but I have revised and attached the spreadsheet with the columns flipped and your formula included. The only problem is for parameter 1, as I am trying to find the next largest value or equal value in increments of .5 for Column B based on cell C3 (parameter 1). I tried modifying the "match type" field in the match function to -1 to find the next greatest value but for some unknown reason to me it is value not available error. When I change the match type to 1 it will find the next smallest value in the .5 increment, but for some reason the -1 match type is not working.

Here is my formula adapted for your spreadsheet:

Please Login or Register  to view this content.

Hi Glen,

That answer is correct with the 9' but the formula won't work when the input height is say 9.3 ft. It needs to find the next highest height 9.5 and use the capacities in that row. For some reason a match type of -1, which should accomplish this, is giving me a value not available error.

Hey JeteMC, that works!! Thank you so much!

Your welcome and thank you for the feedback. Please remember to mark the thread 'Solved', and acknowledge everyone who contributed to the thread by adding to their reputation. Hope that you have a good day.