XLOOKUP: Multiple criteria but one criteria is a value and may not be an exact match

[cheewah0]

Hi everyone,

I need help solving a query where I am looking for prices of a database (internet bandwidth prices) but the bandwidth may not be an exact match.

So the lookup criteria is:
a. Product: [Exact match] DIA or BB
b. Country: [Exact match] In my example I am looking up United Kingdom
c. City: [Exact match] In my example I am looking up London
d. Download speed: [Can be exact or not exact match] In my example, I can solve this issue if I am looking up an exact match of 100Mbps


My question is what is the formula if I wanted to find 150 instead of 100. As there is no 150, I need it to return the price of the next larger alternative which in this case is 200Mbps for €500. I have used XLOOKUP with matchmode of 1 for larger return but I don't even know how to split this data as the search has to be narrowed to Product, Country and City.

Many thanks in advance!

 

[StephenCrump]

Welcome to the Forum!

How about something like this?

	A	B	C	D	E	F	G	H
1
2	DIA	United Kingdom	London	10	$270		Lookup
3							DIA
4							United Kingdom
5	DIA	United Kingdom	London	20	$427		London
6							150
7
8	DIA	United Kingdom	London	100	$500		Result
9							$650
10	DIA	United Kingdom	London	500	$800
11							In more detail ..
12							Speed	Price
13	DIA	United Kingdom	London	200	$650		200	$650
Sheet1

Cell Formulas
Range	Formula
G9	G9	=IFERROR(TAKE(SORT(FILTER(D2:E13,(A2:A13=G3)*(B2:B13=G4)*(C2:C13=G5)*D2:D13>=G6),1),1,-1),"n/a")
G13:H13	G13	=IFERROR(TAKE(SORT(FILTER(D2:E13,(A2:A13=G3)*(B2:B13=G4)*(C2:C13=G5)*D2:D13>=G6),1),1),"n/a")
Dynamic array formulas.