Help with index or xlookup

[brianwhorton]

Hello, i am trying to figure out how to move information from two worksheets to one worksheet. I have worksheet FINAL with three columns code, location, and office. the location and office columns are blank. The office worksheet lists the office associated with the code. The location worksheet lists the location associated with the code.

What i am trying to do is write a formula in columns B and C of the FINAL worksheet, that will look at the OFFICE worksheet and the LOCATION worksheet, then populate columns B and C of the FINAL worksheet. I tried index and xlookup but it never works.

thanks
Brian

[HansV]

In B2 on the Final sheet:

=XLOOKUP(A2:A37,Location!A2:A10,Location!B2:B10,"")

and in C2:

=XLOOKUP(A2:A37,Office!A2:A10,Office!B2:B10,"")

The formula result will automatically spill to row 37.

[brianwhorton]

Hans,
So it appears i was not putting the " " at the end of the formula. What does those " " do?
Brian

[HansV]

The last argument of XLOOKUP is the value to return if there is no match. For example, there is no location for code YZ, so the location XLOOKUP then returns an empty string "".

S2506.png

[brianwhorton]

Great, thank you sir.