Lookup first by description then by price range

[bcbuck88]

Hello,

This is my first post regarding an issue I've had for some time. I'm looking for formula that provides the Item # after first finding the correct Type then finding where the cost falls within that range. I'm thinking that a combination a Index/Match and vlookup would be best. There are hundreds of products and the cost range can vary greatly. Any assistance would be greatly appreciated!

Description:	Product 1
Cost:	$30
Item #:	?
Description	Min Cost	Max Cost	Item #
Product 1	1	25	100001
Product 1	26	50	100005
Product 1	51	75	100030
Product 2	1	25	100033
Product 2	26	50	100004
Product 2	51	75	100009

<tbody>
</tbody>

 

[AhoyNC]

See if this works for you.RL-
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D
1	Description:	Product 1
2	Cost:	$30
3	Item #:	100005
4
5
6
7	Description	Min Cost	Max Cost	Item #
8	Product 1	1	25	100001
9	Product 1	26	50	100005
10	Product 1	51	75	100030
11	Product 2	1	25	100033
12	Product 2	26	50	100004
13	Product 2	51	75	100009
Sheet

 

[etaf]

try
=IFERROR(LOOKUP(1E+307,1/((B1=A6:A11)*(B2>=B6:B11)*(B2<=C6:C11))*D6:D11),0)
but you need to change $30 to 30 - so its a number

 

[etaf]

Excel Workbook
	A	B	C	D
1	Description:	Product 1
2	Cost:	30
3	Item #:	100005
4
5	Description	Min Cost	Max Cost	Item #
6	Product 1	1	25	100001
7	Product 1	26	50	100005
8	Product 1	51	75	100030
9	Product 2	1	25	100033
10	Product 2	26	50	100004
11	Product 2	51	75	100009
Sheet1

 

[bosco_yip]

Another possible,

In B3,

=LOOKUP(B2,OFFSET(B5:D11,MATCH(B1,A6:A11,0),,COUNTIF(A6:A11,B1)))

Regards
Bosco

 

[bcbuck88]

Wayne,

Thanks so much for the formula it works great! Could we adjust it to include other columns such as Column E (Discount Code) or Column F (Effective Date)?

I've tried changing the array D6:D11 but that returns an error.

=IFERROR(LOOKUP(1E+307,1/((B1=A6:A11)*(B2>=B6:B11)*(B2<=C6:C11))*D6:D11),0)

Thanks again!
Brian

 

[bcbuck88]

Thank you both to Ahoy and Bosco but I couldn't get either to work. I guess I need more work on Index and Offset :P

Best,
Brian

 

[etaf]

=iferror(lookup(1e+307,1/((b1=a6:a11)*(b2>=b6:b11)*(b2<=c6:c11))*e6:e11),0)

=iferror(lookup(1e+307,1/((b1=a6:a11)*(b2>=b6:b11)*(b2<=c6:c11))*f6:f11),0)

 

[etaf]

Excel Workbook
	A	B	C	D	E	F
1	Description:	Product 2
2	Cost:	30
3	Item #:	01/06/2017
4
5	Description	Min Cost	Max Cost	Item #	Discount Code	Effective Date
6	Product 1	1	25	100001	1	01/02/2017
7	Product 1	26	50	100005	2	01/03/2017
8	Product 1	51	75	100030	3	01/04/2017
9	Product 2	1	25	100033	4	01/05/2017
10	Product 2	26	50	100004	5	01/06/2017
11	Product 2	51	75	100009	6	01/07/2017
Sheet1

 

[bcbuck88]

Not exactly sure why but those formulas were not working for me. It may be because the values in columns E and F are text.

What I ended up doing was a simple Vlookup from the Item # to look up the values in the columns.

Thanks for your help, you're a life saver!