need assistance referencing data from one sheet to another dependent on different conditions

Question

I need help. I have a google form to populate the responses into Google Sheets. in google sheets I have the sheet with the results and a master sheet that I want to search the results sheet to find a reference number across certain columns of the sheet sorted by date and if the value is found then for it to reference a specific cell value from the row that result was found in. any help would be VERY much appreciated!

https://docs.google.com/spreadsheets/d/1OPvK5QSniZEwHhR60zT-4L8IB64A_ZxfOqpcc0rwkX0/edit?usp=sharing

I've been trying h/vlookup and index match but I'm quite a novice at this so I'm probably doing something wrong.

Basically, the scope of this is to track checked in/out assets showing their status and where they are.

Answer 1

=ARRAYFORMULA(IFERROR(VLOOKUP(B3:B, QUERY(TRIM(SPLIT(TRANSPOSE(SPLIT(
 QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF('Asset Movements'!G2:O<>"", "♦"&'Asset Movements'!G2:O&"♠"&'Asset Movements'!B2:B, ))
 ,,999^99)),,999^99), "♦")), "♠")), "OFFSET 1", 0), 2, 0)))