Look up based on a "type" and also a value within a target range

[Amaelcas]

Hi!

I'm trying to make this formula using index and match but I have a missing part. Here's what I'm trying to get:

I have 2 columns on my sheet 1: Type and Size. Type contains values like: Gate, Butterfly.. etc (Valves type)
Size is just a number

On my sheet 2 I have the columns Classification, Type and Minimum and maximum sizes.

My problem is, in the sheet 1 I need to get the right classification from sheet 2 based on the type and the size, which will be a value between the minimun and maxmiun from sheet 2 but always taking in consideration the type.

Right now I have this formula but of course is not choosing the right row:

=INDEX($E$30:$H$65,MATCH(B168,$E$29:$E$65,0),4)
=INDEX(TablerangeInSheet2,Match(TypeFromSheet1,$TypeColumnInSheet2,0),TheColumnIWantToGet)

Please let me know any question you have and I'll try to clarify the best I can.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #1 requires good titles. "Help with a formula" does not tell us about your question. I have updated it for you this time because you are a new member. --6StringJazzer

[ben_hensel]

Assuming that the rows in sheet two are all unique, we can use SUMPRODUCT to add up all the Row values that match the options you need. So if there are multiple matches it will return something you don't expect, because it will be adding up multiple Rows, but if they're unique it will be adding up only one value (hopefully, the correct one).

Anyway something like this:

Please Login or Register  to view this content.

As an aside, in your shoes I would probably clean up the min and max values to be more real, like make VLV-BFV-S go from 2 to 4.5, and then test "min<=size" instead of "min<size" for example, while VLV-BFL-M goes from 5 to 7. This means that if someone puts in a 4 3/4" valve it would throw an error code as being unspecified, but like, yeah nobody sells that so it's still good information. Likewise VLV-BFV-XXXL will probably top out at 36 or 48 instead of, as shown here, 16 miles across.