Approximate Match With XLOOKUP Two Criteria Returns Multiple Results

[wsnyder]

Hi all,

I am trying to use XLOOKUP with 2 criteria to return an approximate match (Match Mode = -1)
I believe the formula below should return 1 at 0.6, but as you see, it is returning 1 at both 0.8 and 1 (Selected).
Any idea why that would be and the correct fix?

Thanks,
-w

Resp	Rand	Value
1	0.797367	1
2	0.717577	1

Formula:

=XLOOKUP(E3:E7,IF(tblData[Resp]=C3,tblData[Rand]),tblData[Value],0,-1)

 

[Peter_SSs]

Some of the descriptions in the XLOOKUP help I think are misleading as it does not do an 'approximate (closest)' match.
Your 4th argument (-1), as detailed in the help means that if the value is not found, it looks for the next smaller item (not the closest item). Since there is no exact 0.6 in your data & there is no smaller value in your table, the formula is correctly returning the "if not found" value of 0.

S

 

[wsnyder]

Thanks Peter,

I tried so simplify to 1 criteria by adding the weight to the Response_Nbr - now I only have 1 criteria.
I then change the XLOOKUP() to VLOOKUP with approximate match.

I'm still getting a positive response for 1.8 and 1.99 where I expect both to be a negative response.
The only positive response should be at 1.6 since the value in the lookup table is 1.797367.

Any ideas?

Book1
	B	C
2	Cell	Formula
3	G3	=IFERROR(VLOOKUP(F3,tblData[[Resp_Rand]:[Value]],2,TRUE),0)
Sheet1

Lookup Table:

Math Analysis Table 174.xlsx
	S	T	U	V
2	Resp	Rand	Resp_Rand	Value
3	1	0.797367	1.797367	1
4	2	0.717577	2.717577	1
5	3	0.392517	3.392517	1
6	4	0.09832	4.09832	1
7	5	0.938286	5.938286	1
8	6	0.967612	6.967612	1
9	7	0.57408	7.57408	1
10	8	0.382865	8.382865	1
11	9	0.222433	9.222433	1
12	10	0.847763	10.84776	1
13	11	0.502025	11.50203	1
14	12	0.107977	12.10798	1
15	13	0.836595	13.83659	1
16	14	0.695519	14.69552	1
17	15	0.254919	15.25492	1
18	16	0.158783	16.15878	1
19	17	0.388067	17.38807	1
20	18	0.014611	18.01461	1
21	19	0.530713	19.53071	1
Sheet1

Cell Formulas
Range	Formula
U3:U21	U3	=[@Resp]+[@Rand]

 

[Peter_SSs]

I'm still getting a positive response for 1.8 and 1.99 where I expect both to be a negative response.

What does a 'positive response' or a 'negative response' actually mean?

You have posted a formula which refers to cell F3 but we have no idea what is in F3.
We also do not know what result that formula gave for the sample data provided.
We also do not know what result you expected/want from that formula for the sample data provided.

 

[wsnyder]

Thanks Peter,

Here is my Lookup formula:

=IFERROR(VLOOKUP(F3,tblData[[Resp_Rand]:[Value]],2,TRUE),0)

In Col G of this table:

When an approximate match is found I would like to return the value of 1.
From the Lookup Table, I see the value 1.797367 (Table above - last post)
That is closest to 1.6 without going over, so at 1.6, I would like to return the value of 1 in Col G.
But that is not happening. Currently getting a value of 1 returned for 1.8 and 1.99.

Thanks,
-w

 

[Peter_SSs]

I mentioned before in relation to XLOOKUP but VLOOKUP is similar in this respect. They do not do "approximate" match. With your formula if VLOOKUP cannot find the exact value of 1.6 (& it cannot) then it will look for the value that is lower than 1.6 but closest to 1.6
Since there is no value lower than 1.6 the VLOOKUP returns an error and so the IFERROR part of your formula kicks in and returns the zero.

You have said that you want 1 returned on the 1.6 row (ie in cell G5 in your image)
What result do you want in each of the following cells in that image, and why?
G3
G4
G6
G7

Once I know what values you expect in all 5 cells in column G then I can look at possible alternative formulas for you.

 

[wsnyder]

Thanks Peter,

Since the Lookup Value 1.797367is closest to 1.6 (Cell F5) a value of 1 should be returned to Cell G5.
Since the approximate watch is returned to cell G5, all other cells in the set should be zero (G3, G4, G6, G7)

Thanks,
-w

 

[Peter_SSs]

Since the Lookup Value 1.797367

Now I am more confused if the lookup value is 1.797367

In your earlier formula [=IFERROR(VLOOKUP(F3,tblData[[Resp_Rand]:[Value]],2,TRUE),0)] the lookup value is F3 (1.6)

What is the point of the rest of tblData if you are only looking for the closet value to the first value in that table?

Your attempted formulas are looking up something in tblData but your descriptions seems to keep looking up something (closest value) in F3:F7

I wonder if a new set of sample data and expected result would help clarify??