Lookup and return multiple values|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hello,

I'm trying to make a spreadsheet for structural steel detailing. Structural bolts are only threaded over part of their length. I'd like to be able to put in the "grip" which is the thickness of the plates I'm connecting, and have the sheet return which bolt lengths would work. There could be up to three depending on the input value. So if I have a 1/2" dia. bolt and the pieces I'm connecting are each 1/2", 1" total thickness, I could use a 1.5" long bolt, a 1.75" long bolt, or a 2" bolt.

I found this post, which I thought would be the answer: https://www.myonlinetraininghu.....le-matches

It uses this syntax:

=IFERROR(INDEX($A$5:$B$11,SMALL(IF($A$5:$A$11=$E$4,ROW($A$5:$A$11)-4),ROW(A1)),2),"")

I tried replacing the IF($A$5:$A$11=$E$4 with this: IF(AND($B$1:$B$11<$E$4,$C$5:$C$11>$E$4) , where B is the minimum "grip" thickness and C is the maximum grip thickness.

This seems to be the cause of my issue. Can I not put greater than or less than equations in this type of equation? Is there another way I could do this?

I appreciate any help you could provide!