How to consolidate results from different sheets when rows are in random order?

Screenshot that will explain better than I am https://ibb.co/4ZC4Lbd4

How to return the results from different sheet into one table, my bare minimum knowledge of power query says it won’t work because it will stack one table on top of another, but I don’t want that, because everything in a random order

Can I use a formula of multiple ‘IF’ that checks if an item is found on sheet #2 and returns that result, and if not found on sheet #2 then search for it on sheet #3 and return the result from there?