2

Please see the link to my Google Sheet.

Google Database

This is an image from my 'Planning' sheet:

Planning Sheet

In cell A6 of the 'Planning' sheet is a name that is selected by dropdown sourced from column A5:A17 of the 'Qualifications' sheet.

In cell B2 of the 'Planning' sheet is a dropdown and the dropdown data is sourced from row J4:AF4 of the 'Qualifications' sheet.

Cell B6 of the 'Planning' sheet is to contain the formula.

This is an image of my 'Qualifications' sheet:

Qualifications Sheet

Depending on the value selected in the dropdown and the name in column A of the 'Planning' sheet, I want cell B6 of the 'Planning' sheet to return the contents of the cell in the range 'Qualifications'!A5:AF adjacent the name e.g. if the dropdown in A6 is set on David Bowie and 3465 is selected in B2, I want cell B6 to return '29/03/2019'. I'm just not sure about the formula and would appreciate some help, please.

player0
  • 124,011
  • 12
  • 67
  • 124
McChief
  • 423
  • 1
  • 3
  • 18

2 Answers2

1

delete those green cells and paste this in B6:

=ARRAYFORMULA(IFNA(VLOOKUP(A6:A, Qualifications!A5:AF, 
 MATCH(B2:H2, Qualifications!4:4, 0), 0)))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
0

In B6 please try:

=offset(Qualifications!A1,query(arrayformula({row(Qualifications!A5:A14),Qualifications!A5:A14}),"Select * where Col2='" & A6 & "'")-1,query({transpose(arrayformula(COLUMN(Qualifications!B4:G4))),transpose(Qualifications!B4:G4)}, "select Col1 where Col2='" & B5 & "'",0)-1)
user11982798
  • 1,878
  • 1
  • 6
  • 8