1

I thought this would be simple but having a hard time searching because when I look up something like "find value in range with multiple columns" all I get back is how to search for multiple criteria, like first and last names, or an address.

I've got a named range (lets call it letterTiers) where each row is a tier of values, like this:

Tier Value1 Value2 Value3 Value4 Value5
1 aaa bbb ccc
2 ddd eee fff ggg jjj
3 kkk lll mmm nnn

I'm just looking for a function or custom function like this =findTier(value,range) so =findTier("eee",letterTiers) would return 2; =findTier("kkk",letterTiers) would return 3. It could just give me the row number and I can work out the tier based on where the range is in the sheet, or it can reference that tier column, or I can put the tier column on the other side of the range and say give me the 6th column, like how VLOOKUP works.

Another route would be to normalize the table, but I'm scraping the tiers from a website that's updated regularly, and this is how it's formatted. Figured this would be the easiest way to get what I'm looking for.

1
  • With the new functions just rolled out for Google Sheets, you actually can build a function that will be as easy to use as the sample function you used as an explanation. I encourage you to read up on the new rollouts. If you'd like to share a link to a sample spreadsheet with some data in it, I may be up for building that function. It would just be too time consuming to explain here, and too difficult for you to then follow and build. It'd be easier to build it directly in the spreadsheet.
    – Erik Tyler
    Sep 18, 2022 at 5:28

2 Answers 2

0

If you've got the above table in A1:F4 of a sheet, then the following formula performs the lookup you want. For instance:

=arrayformula(vlookup("eee",query(split(flatten(B2:F4&"|"&A2:A4),"|"),"where Col2 is not null"),2,false))

N.B. I can't quite work out what your named range actually corresponds to, so I've directly referred to the cell references. You should be able to extend the ranges as required depending on the dimensions of the source.

1
  • It didn't work directly on my actual sheet, but when I recreated my sample table above it worked. Error said the vlookup couldn't find the value I was looking for. So I used the "query(split(flatten(" to normalize the first table, then did the vlookup on that new range. Not sure why it didn't work all in one cell. Thanks!
    – cu5ya7
    Sep 17, 2022 at 20:56
1

The vlookup() function will only return the first match. In the event the search key may appear in more than one row, you can use filter(), like this:

=filter( 
  A2:A, 
  byrow( 
    B2:F, 
    lambda( 
      row, 
      sum(sign(row = "eee")) 
    ) 
  ) 
)

Replace "eee" with a reference to a cell to make it easier to look for different values.

If you are using this search a lot, you may want to create a named function.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.