How do I find a value in a specific range based on a criteria?-CodePudding

I am trying to find the "km" value which applies to F2 in the range of D2 to E2 in column A.

I am NOT able to switch column A and B so the "gradient" is to the left of the "km".

Excel screenshot

CodePudding user response：

In cell G2:

If you want the first match:

=IFERROR(INDEX(A2:A28,MATCH(1,INDEX((A2:A28>=D2)*(A2:A28<=E2)*(B2:B28=F2),),0)),"No Match")

If you want the last match:

=IFERROR(LOOKUP(2,1/((A2:A28>=D2)*(A2:A28<=E2)*(B2:B28=F2)),A2:A28),"No Match")

CodePudding user response：

You may use arrays to get the values:

Notice my formula gets the MAX KM in case there are more than 1 match, but always inside the range delimited by D2 and E2. You can change MAX to MIN,SUM, AVERAGE or whatever you need.

Also, please notice that depending on your Excel version you may need to enter the formula as an array formula so if you got and old version, introduce it pressing CTRL SHIFT ENTER

=MAX(IF(B2:B28=F2;IF(A2:A28<=E2;IF(A2:A28>=D2;A2:A28))))

Actually, you can combine this with an aditional IF to check if there is any match first or not with COUNTIF:

=IF(COUNTIFS(B2:B28;F2;A2:A28;">="&D2;A2:A28;"<="&E2)=0;"No match";MAX(IF(B2:B28=F2;IF(A2:A28<=E2;IF(A2:A28>=D2;A2:A28)))))