Xlookup - empty cells

[lezawang]

Hi
Why f3:f5 values is 2 when the quantity cells E3:E5 are empty? Thank you very much.

Book1
	A	B	C	D	E	F
1	Quantity	Unit Price			Quantity	Price
2	1	$ 100			22	90
3	10	$ 90				2
4	50	$ 80				2
5	100	$ 50				2
6	200	$ 40
7	300	$ 30
8	500	$ 20
9	600	$ 10
10	800	$ 5
11	1000	$ 2
12
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=XLOOKUP(E2,$A$2:$A$11,$B$2:$B$11,"",-1)

 

[MARK858]

The -1 option that you are using is defined as

-1 - Exact match. If none found, return the next smaller item.

 

[Fluff]

Because you are not doing an exact match. As E3 is empty it returns the next smallest value to "" which is the largest number in col A.

 

[lezawang]

The next smallest value of the Quantity or the Price?

This is how I understand it:

E3 = empty (quantity is empty)

Now go to the main table (A1:A10) and search for E3= empty ... will not find any match. Then it should go with an approximate match. I was thinking it should go with Quantity = 1 and the price is 100. But it seems it match E3 = empty with A11 which has the value of Price is 2. That how Vlookup works, no? Thank you.

 

[MARK858]

That how Vlookup works, no?

But you aren't dealing with Vlookup

XLOOKUP function - Microsoft Support

Use the XLOOKUP function when you need to find things in a table or a range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term, and return a result from...

support.microsoft.com

 

[lezawang]

sorry I meant Xlookup!

 

[Fluff]

It would appear that Xlookup treats an empty cell as "" which is text & therefore greater than any number.

 

[lezawang]

Thanks, that is really better. I was questioning my Xlookup understanding. Thanks all.

 

[Fluff]

Glad we could help & thanks for the feedback.