Match 1 Value within Multiple Ranges and return a corresponding value

[jnslavich]

ID Range1 Range2 Value1 Match
1_ 0_____ 99____ 100___ 2
2_ 100___ 199___ 150___ 2
3_ 200___ 299___ 200___ 3
4_ 300___ 399___ 300___ 4
5_ 400___ 499___ 350___ 4


What I'm looking for is a formula which will take Value1 and check to see if it is between a range named Range1 and Range2.

IF TRUE then return the corresponding ID in the Match Column, IF FALSE then move to the next row with Range1 and Range2.

 

[T. Valko]

ID Range1 Range2 Value1 Match
1_ 0_____ 99____ 100___ 2
2_ 100___ 199___ 150___ 2
3_ 200___ 299___ 200___ 3
4_ 300___ 399___ 300___ 4
5_ 400___ 499___ 350___ 4


What I'm looking for is a formula which will take Value1 and check to see if it is between a range named Range1 and Range2.

IF TRUE then return the corresponding ID in the Match Column, IF FALSE then move to the next row with Range1 and Range2.

Try this...

=INDEX(A$2:A$6,MATCH(D2,B$2:B$6))

Copy down as needed.

 

[T. Valko]

Try this...

=INDEX(A$2:A$6,MATCH(D2,B$2:B$6))

Copy down as needed.

If the IDs really are the sequential numbers starting with 1 then you can use this version:

=MATCH(D2,B$2:B$6)

 

[jnslavich]

Try this...

=INDEX(A$2:A$6,MATCH(D2,B$2:B$6))

Copy down as needed.

ID Range1 Range2 Value1 Match
1_ 0_____ 99____ 100___ 2
2_ 100___ 199___ 150___ 2
3_ 200___ 299___ 200___ 3
4_ 300___ 399___ 300___ 4
5_ 400___ 499___ 350___ 4
6_ 500___ 599___ 700___ 2

This formula might be correct; however it returns an ID for cases where it is provably false such as the above example where Value1 has no match but it still returns an ID. In the above example on D7 there is no match, but it still returns a value.

 

[T. Valko]

ID Range1 Range2 Value1 Match
1_ 0_____ 99____ 100___ 2
2_ 100___ 199___ 150___ 2
3_ 200___ 299___ 200___ 3
4_ 300___ 399___ 300___ 4
5_ 400___ 499___ 350___ 4
6_ 500___ 599___ 700___ 2

This formula might be correct; however it returns an ID for cases where it is provably false such as the above example where Value1 has no match but it still returns an ID. In the above example on D7 there is no match, but it still returns a value.

Can you show some examples?

 

[T. Valko]

Can you show some examples?

P.S.

With those formulas, any lookup value that is greater than the max range value in the table will "match" the max range value.

 

[jnslavich]

Can you show some examples?

ID Range1 Range2 Value1 Match
1_ 0_____ 99____ 100___ 2
2_ 100___ 199___ 150___ 2
3_ 200___ 299___ 200___ 3
4_ 300___ 399___ 300___ 4
5_ 400___ 499___ 350___ 4
6_ 500___ 599___ 700___ 2

In this table above, row 7 has been added to illustrate the example.

For Row 7, the lookup value is 700. 700 is checked to see if it falls between 0-99(False), 100-199(False), ... , and finally 500-599(False). So if there is no case where 700 fits within these range values then it should return no result. But instead returns a value from the ID column, and in this case I believe it would return a 2.

 

[T. Valko]

ID Range1 Range2 Value1 Match
1_ 0_____ 99____ 100___ 2
2_ 100___ 199___ 150___ 2
3_ 200___ 299___ 200___ 3
4_ 300___ 399___ 300___ 4
5_ 400___ 499___ 350___ 4
6_ 500___ 599___ 700___ 2

In this table above, row 7 has been added to illustrate the example.

For Row 7, the lookup value is 700. 700 is checked to see if it falls between 0-99(False), 100-199(False), ... , and finally 500-599(False). So if there is no case where 700 fits within these range values then it should return no result. But instead returns a value from the ID column, and in this case I believe it would return a 2.

Try this...

Book1
	A	B	C	D	E
1	ID	Range1	Range2	Value1	Match
2	1	0	99	100	2
3	2	100	199	150	2
4	3	200	299	200	3
5	4	300	399	300	4
6	5	400	499	350	4
7	6	500	599	700
Sheet1

Formula entered in E2 and copied down:

=IF(D2>MAX(B$2:C$7),"",INDEX(A$2:A$7,MATCH(D2,B$2:B$7)))

Or:

=IF(D2>C$7,"",INDEX(A$2:A$7,MATCH(D2,B$2:B$7)))

 

[jnslavich]

thank you so much biff, you saved my butt

 

[T. Valko]

thank you so much biff, you saved my butt

You're welcome. Thanks for the feedback!