CODEWITHSUNDEEP
excel
Johannes Forsting
Johannes Forsting

Reputation: 9

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

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

Upvotes: 0

Views: 73

Answers (2)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

You may use arrays to get the values:

enter image description here

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)))))

Upvotes: 0

tigeravatar
tigeravatar

Reputation: 26640

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")

Upvotes: 1

Related Questions