What formula should I use if I were to search up with a specific criteria to return a value?

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:

  1. I have a list of classrooms, each assigned with a class and its class code,
  2. 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.

Leave a Reply