comparing serial numbers from 10 sheets and returning data validation drop down list entries to main

[Doyle500]

HI,

I have a main list on sheet 1 with 239 devices. Column D has the serial numbers on main list. all the other 9 sheets are by building and have the serial numbers in column C, but just for devices in that building. on the building sheets I have data validation drop down list for 2 columns L and M. I am trying to make it so when team members update the device status by serial number and enters data by drop down list in columns L and M on building sheets, it auto populates the main sheet by finding the matching serial number and putting the data validation entry in both columns P and Q with the same name on main sheet. I have tried index (match)  if error with match to no avail. any help would be most appreciated. I have googled and YouTube to no avail. the =(cell you want to copy) enter wont work because of data validation error.  

[#2]

Hi@Doyle500,

always assuming I have understood your problem correctly, I would solve it as follows. There may be a simpler solution in the future with VSTACK(), but this is not yet available to me. Therefore I have created an auxiliary column in column 0 of the main sheet, in which I first search the 9 buildings sheets for the serial number with an IFS() function. Then you can use INDIRECT() to build the right cell reference for XLOOKUP().

See also my attached example document. Please see if my example corresponds to your ideas.

[#3]

Hi @dscheikey
first I want to thank you for your response. It looks like this will work only thing is when I do the ifs () formula on my spread sheet it returns a spill error. I was able to see the Xlookup () formula but not the ifs () formula. if you could write it out so I have a visual reference as I am a newbie to writing formula's like this. thank you very much for any help in this matter.
respectfully,
Doyle 500

[#4]

@Doyle500It is easier if you watch it on your own computer, because then the correct regional setting is used. Here in German: