Example sheet link : https://docs.google.com/spreadsheets/d/14Dy-VKL7xHlH5KpZnJkWjNPDM6g5lkKZReakyJGTdj0/edit#gid=605445197
Hello, I am fairly new to sheets and I was wondering what I should use for the following case:
- I have a list of classrooms, each assigned with a class and its class code,
- I have a dropdown list based on the list of classrooms,
For such case, what should I use if I wanted to return the assigned class and class code based on the value select from the dropdown list?
>Solution :
Check out the formula I just added to that sheet. It’s:
=iferror(index(C:C, match(H4, B:B, 0)), "")
INDEX returns a row from an array* when given an index. MATCH returns the index of the matching record from an array. 0 means exact match, but there are other options.
In my opinion, INDEX+MATCH is superior to VLOOKUP, which is what a lot of people use.
* actually, just one cell, but the column reference is optional and people usually pass single column arrays to it. If you define a multi-column range for the first argument of INDEX, you can specify the row and column, either of which you can determine with MATCH, which makes INDEX+MATCH very versatile.