Search code examples
google-sheetsdrop-down-menugoogle-sheets-formulagoogle-sheets-query

Dynamic drop-down based on Index & Match variable

I have a relatively simple search (no index keys possible) for a license plate in a log using cell A15. To the right cells, B15 and C15 auto-populate perfectly using index and match functions.

enter image description here

https://docs.google.com/spreadsheets/d/1eKIF3aeY-yUlG9RpRxP8YWYm9Vj78SYWut3B9GGNOhU/edit?usp=sharing

There are only a couple of problems. First, there are times when a different driver might be driving the same vehicle, so a drop down to choose the relative row would be good. Note that at least 3 people are driving Frank's truck, yet the only reference we have for search criteria is the license plate.

Huge bonus if someone could explain how I can use the same cells (A15, B15 & C16) with multiple Index & Match functions so that either the license plate OR the name could be used as the criteria to autofill fill the other data.

=index(B2:B12,MATCH(A15,A2:A12,0))

Solution

  • B15:

    =QUERY({A2:C13}, "select Col2,Col3 where Col1='"&A15&"'", 0)

    B24:

    =QUERY({A2:C13}, "select Col1,Col3 where Col2='"&A24&"'", 0)

    0

    to get unique values only use UNIQUE:

    =UNIQUE(QUERY({A2:C13}, "select Col1,Col3 where Col2='"&A24&"'", 0))