excel formula lookup

[ariel20029]

I need to pull a product ID based on if the number of users and a specific product are in a range of values. I tried a few formulas with no luck.

ID	Users	PROD ID			ID	USER LOW	USER HIGH	PROD ID
A	1				A	1	100	AB1
A	100				A	101	200	AB2
B	150				A	201	300	AB3
B	10				B	1	100	BA1
C	300				B	101	200	BA2
					B	201	300	BA3
					C	1	100	CA1
					C	101	200	CA2
					C	201	300	CA3

<COLGROUP><COL style="WIDTH: 48pt" span=9 width=64><TBODY>
</TBODY>

 

[Tom Urtis]

What are your expected results in the yellow cells? Not clear how the formula should interpret the low and high ranges.

 

[ariel20029]

What are your expected results in the yellow cells? Not clear how the formula should interpret the low and high ranges.

I am looking for the product ID to return. Thats the issue I am having finding a formula to work on the range. I did it a very long time ago but cant remember how I did it.

 

[Special-K99]

Looks like you need an INDEX(...MATCH(...IF...)))
but I cant get it to work...

 

[ariel20029]

Looks like you need an INDEX(...MATCH(...IF...)))
but I cant get it to work...

Thank you for trying

 

[Special-K99]

Gottit!

Lookup table is A1:D9

Yellow user table is A11:B14

=INDEX(A$1:D$9,MATCH(A11,IF(B11>=B$1:B$9,IF(B11<=C$1:C$9,A$1:A$9,"")),0),4)
Array formula, use Ctrl-Shift-Enter

Note: You MUST use two IFs one behind the other as above, I was trying to use AND(B11>=B$1:B$9,B11<=C$1:C$9) and it was failing. I recollect someone explaining to me on a forum why you couldnt use the AND but I cant recall why, anyway the above works if you set the location of the tables as specified.

 

[ariel20029]

Gottit!

Lookup table is A1:D9

Yellow user table is A11:B14

=INDEX(A$1:D$9,MATCH(A11,IF(B11>=B$1:B$9,IF(B11<=C$1:C$9,A$1:A$9,"")),0),4)
Array formula, use Ctrl-Shift-Enter

Note: You MUST use two IFs one behind the other as above, I was trying to use AND(B11>=B$1:B$9,B11<=C$1:C$9) and it was failing. I recollect someone explaining to me on a forum why you couldnt use the AND but I cant recall why, anyway the above works if you set the location of the tables as specified.

thank you so much,
I will give it a shot.
Sharon

 

[Special-K99]

See highlighted section D11

https://www.filesanywhere.com/fs/v.aspx?v=8b6a678d58656faca0aa