Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Xlookup syntax

0

Hello, i wanna ask about xlookup formula here that i applied in column G, sheet Orders.
Why the xlooup result gives error unlike the vlookup result? and how the correct xlookup formula shoul be that gives the expected result like the vlookup? 

Thanks in advance, I'm waiting your reply.

Answer
Discuss

Answers

0
Selected Answer

Fida

Your XLOOKUP formula is incorrect. You want to look up the CustomerID from column B of worksheet Orders (that gives an index number, the row number) in the Customers sheet.

In the case of cell G2, the formula is shown below but you are looking in the same sheet/ column (so get the row where 1 appears in the worksheet Orders rather than in Customers). This gives some correct results (by chance) but goes wrong at G2 for example.

=XLOOKUP(B2,B:B,Customers!B:B,,0)

You need to look in column A of the Customers sheet instead, so change it to:

=XLOOKUP(B2,Customers!A:A,Customers!B:B,,0)

Copy that down and your results will match. It will look up the ID in column A of Customers and return what's in the same row but column B of same.

If you want to report errors, XLOOKUP has a fourth parameter (which you omitted) to give a text when an exact match isn't found. Change B2 to this (and copy down) then the cell will read "NOT FOUND" if the CustomerID isn't found.

=XLOOKUP(B2,Customers!A:A,Customers!B:B,"NOT FOUND",0)

If you want such cells even more evident, you could use Conditional Formatting to fill the cell with a colour of change the font colour.

Hope this helps.

Discuss

Discussion

Hello John, thanks alot for the correction. You're awesome!
Fida_mutia (rep: 22) Jul 7, '22 at 2:35 am
Thanks Fida. Glad that helped.
John_Ru (rep: 3992) Jul 7, '22 at 4:16 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login