XLookup With Multiple Criteria

[acool]

Hi Everyone,

I am currently trying to complete an XLookup based on the values in Column in G and H. If Column H matches the Value in Column A (Merged Cells) and the value in column G falls between the values Column in B & C, I would like to return the price in Column E. Any help/insight would be greatly appreciated! Thank You!

 

[Anfinsen]

If I am reading your data correctly, none of the data above would return a price from Column E, is that an accurate statement?

 

[awoohaw]

Need to unmerge and repeat the values in column A.
I may have typos, If you need this flushed out better please post a mini worksheet using the xl2bb (link below) add in.
Images help describe the problem, but the forum need to manually create scenarios from images.
If you can't use the xl2bb then please post the data and expected results as a table.

=INDEX($A$3:$A$12,(--($B3>=$G$3:$G$12))*(--($B3<=$G$3:$G$12))*(Row($H$3:$H$12)-3)

 

[acool]

If I am reading your data correctly, none of the data above would return a price from Column E, is that an accurate statement?

@Anfinsen As an example, an Oversized Item with a weight of 4.2 Lbs should return a value of $9.43

 

[awoohaw]

correction:

=INDEX($E$3:$E$12,(--($B3>=$G$3:$G$12))*(--($B3<=$G$3:$G$12))*(Row($H$3:$H$12)-3)*(--($H3 = A$3:$A$12))

 

[acool]

correction:

=INDEX($E$3:$E$12,(--($B3>=$G$3:$G$12))*(--($[B]C3[/B]<=$G$3:$G$12))*(Row($H$3:$H$12)-3)*(--($H3 = A$3:$A$12))

@awoohaw Shouldn't it be

(--($B3>=$G$3:$G$12))*(--($C3<=$G$3:$G$12))

 

[awoohaw]

@awoohaw Shouldn't it be

Probably I was just trying to type it. I didn't have it in a worksheet. The xl2bb mini would help would help.

in cell I3:

=INDEX($E$3:$E$12,(--($B3>=$G$3:$G$12))*(--($C3<=$G$3:$G$12))*(Row($I$3:$I$12)-3)*(--($H3 = A$3:$A$12))

 

[KevCarter]

Is this the right idea?

XLookup Answer.xlsx
	A	B	C	D	E	F	G	H
1	Removal Fee per Unit
2
3	Standard Size	0	0.5	$0.97		4.2	Oversize	$9.43
4	Standard Size	0.5	1	$1.46		2	Oversize	$4.07
5	Standard Size	1	2	$2.20		2	Oversize	$4.07
6	Standard Size	0	2	$2.83		2	Oversize	$4.07
7	Oversize	0	1	$3.12		2	Oversize	$4.07
8	Oversize	1	2	$4.07		0.4	Standard Size	$0.97
9	Oversize	2	4	$5.56		2	Oversize	$4.07
10	Oversize	4	10	$9.43		2	Oversize	$4.07
11	Oversize	0	10	$13.05		14	Oversize
12	Oversize					0.4	Oversize	$3.12
Sheet1

Cell Formulas
Range	Formula
H3:H12	H3	=XLOOKUP(1,($G3=$A$3:$A$12)*($F3>=$B$3:$B$12)*($F3<=$C$3:$C$12),$D$3:$D$12,"")

 

[acool]

Is this the right idea?

XLookup Answer.xlsx
	A	B	C	D	E	F	G	H
1	Removal Fee per Unit
2
3	Standard Size	0	0.5	$0.97		4.2	Oversize	$9.43
4	Standard Size	0.5	1	$1.46		2	Oversize	$4.07
5	Standard Size	1	2	$2.20		2	Oversize	$4.07
6	Standard Size	0	2	$2.83		2	Oversize	$4.07
7	Oversize	0	1	$3.12		2	Oversize	$4.07
8	Oversize	1	2	$4.07		0.4	Standard Size	$0.97
9	Oversize	2	4	$5.56		2	Oversize	$4.07
10	Oversize	4	10	$9.43		2	Oversize	$4.07
11	Oversize	0	10	$13.05		14	Oversize
12	Oversize					0.4	Oversize	$3.12
Sheet1

Cell Formulas
Range	Formula
H3:H12	H3	=XLOOKUP(1,($G3=$A$3:$A$12)*($F3>=$B$3:$B$12)*($F3<=$C$3:$C$12),$D$3:$D$12,"")

This Worked, thank you