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

1
votes

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.

2
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup

2 Answers

1
votes
=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)))

0

1
votes

I think you are looking for this: Search a value from another tab/sheet in google sheets based on cell reference

It creates a search page in google sheets where the user enters a value in one cell(s) and then other cells auto-populate with values from another tab/sheet based on the value entered in the search cell(s). It has a working sheet example. You can try it so that by for example entering your 'Bin Asset No.' in the search cell as reference it will give you the matching 'Bin Size', 'Status'.

The formula shared by @player0 works too.