Google Spreadsheet search in a row

Question

I have table that looks like this:

Name Item1  Date        Item 2 Date
John Orange 2/8/2020    Apple  8/8/2020
Bob  Banana 6/8/2020   

I'm trying to find a way to query that table so that it returns me every item the person has and on what date they got it. Some of the problems I have is that there may be more items added over time so columns would expand.

I have looked into queries but it always forces me to select a column, not a row number. I'm really not sure how to pull this off. I also looked into HLookup but the date field really messes with it.

I am open to changing the structure of the data in the spreadsheet if there is a way to allow to accomplish what I need.

Answer 1

try:

=ARRAYFORMULA(IFNA(VLOOKUP(B13, A1:10, COLUMN(B1:1), 0)))

---

if you want it in a more readable format:

={ARRAY_CONSTRAIN(
  FLATTEN(FILTER(IFNA(VLOOKUP(B13, A1:10, COLUMN(B1:1), 0)), 
  MOD(COLUMN(B1:1),   2)=0)), (COLUMNS(B1:1)-1)/2, 1), 
  FLATTEN(FILTER(IFNA(VLOOKUP(B13, A1:10, COLUMN(C1:1), 0)), 
  MOD(COLUMN(C1:1)-1, 2)=0))}