Connect XLOOKUP to Data on Different Sheet

[JestersQuest]

I love the new XLOOKUP to replace my many Index Match formulas, but I can't seem to connect XLOOKUP to data on another sheet. If the data is on the sheet with the XLOOKUP formula, everything works perfectly. That same data on another sheet returns 0.0. I just can't figure out what I'm doing wrong!

What works:

=XLOOKUP(C4,I:I,J:J)

What doesn't work:

=XLOOKUP(C4,Data!I:I,J:J)

Please help! What am I doing wrong? Or is it not possible to connect to data on another sheet?

Thanks!

[#2]

@JestersQuest Your formula looks OK and works for me. I replicated your example in the attached workbook.

[#3]

@Riny_van_Eekelen thank you for taking the time to help me with this. I still couldn't get it to work, but your test sheet made me realize that Data!I:I was looking in the Data sheet, but J:J was still looking in the current sheet (which didn't have the info I wanted to return). So I was able to test and fix my formula by adding Data!J:J. Much appreciated!

Original Formula

=XLOOKUP(C2,Data!I:I,J:J)

Corrected Formula

=XLOOKUP(C2,Data!I:Data!I,J:J)

[#4]

@JestersQuest Glad you could figure it out!

[#5]

@JestersQuest 

You appear to referencing an entire column on the 'Data' sheet but then returning a cell from the formula sheet.

Edit:

Sorry, I failed to open the discussion up to see the later comments

[#6]

@JestersQuest 

I am having somewhat of the same issue. I have sheet 1 with all the information I need however when i try to populate the data from sheet one it gives all my items the same order number instead of the different order that it is connected to. Example: Order X has different items but the same order. This only pulls one item and not multiple items. 

[#7]

@K_eastman If your question is about why XLOOKUP only returns one value, the answer is "that's by design". If you want to return all relevant records for a particular order number you need to use the FILTER function.