CODEWITHSUNDEEP
google-sheetsspreadsheet
Doug Fir
Doug Fir

Reputation: 21212

Index(match()) or lookup to return first non null / empty value

I'm using an index(match()) function to return a value based on the name of a column header. However, I've recently learned that a column header can appear 4 times in my data. However, there would only ever be one populated, the others would always be empty.

Whilst I could do a nested if =(isblank(...)# do stuff) I wondered if there was a more sophisticated way to tell GSheets to return the first non null / empty value, kind of like the SQL coalesque() function.

Is there?

Current formula looks like this:

=index('Form Responses 1'!$A$2:$DY,match($A2,'Form Responses 1'!$A$2:$A,0),match(I$1,'Form Responses 1'!$A$1:$DY$1,0))

However, this piece is the problem piece:

match(I$1,'Form Responses 1'!$A$1:$DY$1,0)

This is because I$1 appears 4 times in range Form Responses 1'!$A$1:$DY$1. The one that I want is the one that does not return a empty cell when nested in index() function.

Upvotes: 2

Views: 1988

Answers (1)

Max Makhrov
Max Makhrov

Reputation: 18707

I've made a more simple case when all the data is on single sheet.

enter image description here

The formula is:

=QUERY(TRANSPOSE({A1:E1;FILTER(A2:E8,A2:A8 = 2)}), "select Col2 where Col2 is not null and Col1 = 'foo' limit 1")

  • number 2 is criteria by row ($A2 in your sample)
  • text foo is criteria by column ( replace it with " & I$1 & " )

How it works

  • {A1:E1; filter } is to get headers and desired row in one array
  • TRANSPOSE is to use query
  • where Col2 is not null is to get rid of empty cells
  • limit 1 is like select first 1

Upvotes: 2

Related Questions