2 Way lookup with one approximate match

[Nujoma]

Create a formula in cell G21 that returns the reward you receive if you find an item with a cetain weight
Example 1:	You find a 2,5 gram Diamond, you receive 200 €
Example 2:	You find a 7 gram Gold Nugget, you receive 30 €
the maximum weight that can be entered is 20 grams
Unfortunate circumstances dictate that only one lookup table can be used
Item found	weight up to (gr)	Reward (€)		Choose Item:	Gold Nugget
Diamond	1​	100​		Enter Weight:	10​
Diamond	3,5​	200​
Diamond	5​	350​
Diamond	10​	400​		Your reward:
Diamond	20​	500​
Gold Nugget	3​	10​
Gold Nugget	7,5​	20​		wrong solution:	30​
Gold Nugget	10​	30​		=XLOOKUP(G17&G18;Table1[Item found]&Table1[weight up to (gr)];Table1[Reward (€)])
Gold Nugget	20​	50​
Silver Nugget	2​	5​		only works if the exact weight that is
Silver Nugget	5​	10​		present in the weight column is entered.
Silver Nugget	10​	15​
Silver Nugget	15​	20​
Silver Nugget	20​	30​
The method using the "&" described all over the internet
do not work here as the second value is not always exactly the value in the weight lookup column

 

[Nujoma]

Hello, thanks, hat works! QOuld you be able to point me to an explanation?

nope, 9 gram silver should return 15, but it returns "0"

2 column xlookup problem.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		Create a formula in cell G21 that returns the reward you receive if you find an item with a cetain weight
2
3		Example 1:	You find a 2,5 gram Diamond, you receive 200 €
4		Example 2:	You find a 7 gram Gold Nugget, you receive 20 €
5
6
7		the maximum weight that can be entered is 20 grams
8		Unfortunate circumstances dictate that only one lookup table can be used
9
10		Item found	weight up to (gr)	Reward (€)		Choose Item:	Silver Nugget		Dropdown
11		Diamond	1	100		Enter Weight:	9		Diamond
12		Diamond	3,5	200					Gold Nugget
13		Diamond	5	350					Silver Nugget
14		Diamond	10	400		Your reward:	0
15		Diamond	20	500
16		Gold Nugget	3	10
17		Gold Nugget	7,5	20		wrong solution:	#N/A
18		Gold Nugget	10	30		=XLOOKUP(G10&G11;Table1[Item found]&Table1[weight up to (gr)];Table1[Reward (€)])
19		Gold Nugget	20	50
20		Silver Nugget	2	5		only works if the exact weight that is
21		Silver Nugget	5	10		present in the weight column is entered.
22		Silver Nugget	10	15
23		Silver Nugget	15	20
24		Silver Nugget	20	30
25
26		The method using the "&" described all over the internet
27		do not work here as the second value is not always exactly the value in the weight lookup column
28
29
Sheet1

Cell Formulas
Range	Formula
I11:I13	I11	=UNIQUE(Table1[Item found])
G14	G14	=IFERROR(XLOOKUP(G10&G11,Table1[Item found]&Table1[weight up to (gr)],Table1[Reward (€)],,1),"0")
G17	G17	=XLOOKUP(G10&G11,Table1[Item found]&Table1[weight up to (gr)],Table1[Reward (€)])
F18	F18	=FORMULATEXT(G17)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
G10	List	=$I$11#
G11	Whole number	between 0,1 and 20

 

[Ottsel]

I noticed on the 'wrong solution' box it said G17&18, which was odd as it should of been in G10&G11.
Second, the example 2 required it to round up. "Oh, you found a 7 instead of a 7.5? Good enough", which is strange, but the assignment asks for it, so we give it that, so after giving the formula a square bracket we add 2 commas, since [if_not_found] isn't required and move onto [match_mode], which the index or options goes as follows:
0 - Exact match
-1 - Exact match or next smaller item
1 - Exact match or next larger item
2 - Wildcard character match
Since we needed to round up we go with 1, thus making your 7 an acceptable find for a 7.5 reward.

 

[Ottsel]

nope, 9 gram silver should return 15, but it returns "0"

So, you don't round up to the nearest decimal, but rather the next whole number?

 

[Nujoma]

So, you don't round up to the nearest decimal, but rather the next whole number?

Hello,
these values are a given, and I am hoping to find the next largest Number of the weight entered and return the "reward" value for that.... similar to a tax bracket lookup.... find exact OR the next larger number in the second column and return the reward for that...

my examples had an error in it, here are 4 more that hopefully explain it better:

10,1 gram diamond : reward: 500€

9,9 gram gold nugget: reward: 30 €

10 gram gold nugget: reward: 30 €

10,1 gram gold nugget: reward: 50 €

With different tables for each item, I am able to use the indirect function to look up in the tables that are named like the resources, without any issue, but if the data is in ONE table, an exact lookup on the first and an appoximate lookup on the second value seems impossible.... or maybe just a brain f*rt from my side ??

Maybe this helps (if you are a Golfer ) Original problem comes from course handicap lookup tables for golf, each course has a different table, and the steps betwwen the changing handicaps are not in sync....
SO I need a way to look up the (exact) course as text (in my example the "resource") and then an the exact or next larger number for the handicap (in my example the weight of the found resource)

Looking at the tables below, a 3.7 Handicap would have a Course Handicap of 1 on the yellow course, but a Course Handicap of 2 on the red course

Course 1:

Course 2:

 

[Nujoma]

Hello, I was able to solve it, using a filer function in the vlookup

2 column xlookup problem solved.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		Create a formula in cell G14 that returns the reward you receive if you find an item with a cetain weight
2
3		Example 1:	You find a 2,5 gram Diamond, you receive 200 €
4		Example 2:	You find a 7 gram Gold Nugget, you receive 20 €
5
6
7		the maximum weight that can be entered is 20 grams
8		Unfortunate circumstances dictate that only one lookup table can be used
9
10		Item found	weight up to (gr)	Reward (€)		Choose Item:	Silver Nugget					Dropdown
11		Diamond	1	100		Enter Weight:	16					Diamond
12		Diamond	3,5	200								Gold Nugget
13		Diamond	5	350								Silver Nugget
14		Diamond	10	400		Your reward:	20
15		Diamond	20	500			=XLOOKUP(G11;FILTER(Table1[weight up to (gr)];Table1[Item found]=G10);FILTER(Table1[Reward (€)];Table1[Item found]=G10);"not found";-1;1)
16		Gold Nugget	3	10
17		Gold Nugget	7,5	20
18		Gold Nugget	10	30
19		Gold Nugget	20	50
20		Silver Nugget	2	5
21		Silver Nugget	5	10
22		Silver Nugget	10	15
23		Silver Nugget	15	20
24		Silver Nugget	20	30
25
26
27
Sheet1

Cell Formulas
Range	Formula
L11:L13	L11	=UNIQUE(Table1[Item found])
G14	G14	=XLOOKUP(G11,FILTER(Table1[weight up to (gr)],Table1[Item found]=G10),FILTER(Table1[Reward (€)],Table1[Item found]=G10),"not found",-1,1)
G15	G15	=FORMULATEXT(G14)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
G10	List	=$L$11#
G11	Whole number	between 0,1 and 20

 

[Ottsel]

I'm glad you were able to find the solution. Appreciate the update!