Connect XLOOKUP to Data on Different Sheet

Question

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!

jestersquest · Answer

Riny_van_Eekelen&nbsp;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!&nbsp;Original Formula=XLOOKUP(C2,Data!I:I,J:J)&nbsp;Corrected Formula=XLOOKUP(C2,Data!I:Data!I,J:J)&nbsp;&nbsp;

riny_van_eekelen · Answer

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

riny_van_eekelen · Answer

JestersQuest&nbsp;Glad you could figure it out!

peterbartholomew1 · Answer

JestersQuest&nbsp;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 😞

k_eastman · Answer

JestersQuest&nbsp;&nbsp;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.&nbsp;&nbsp;

Forum Discussion

Connect XLOOKUP to Data on Different Sheet

Resources