Return index number of matching item in array

Question

In Excel, how can I return the index number for a matching item in an array?

For example, if one column has:

Apple
Pear
Zebra

and I ask for "Pear", the function should return the numeric value 2. If I ask for "Grape", it should return FALSE or #N/A.

None of the spreadsheet functions I researched (Lookup, VLookup, Match) do this.

Answer 1

Well, the function MATCH does exactly that.

Say you have

Apple
Pear
Zebra

in A1:A3.

Your input is in D1, and output in D2. D1 will have some word, and D2 should be:

=MATCH(D1,A1:A3,0)

Where D1 is the value you are searching, A1:A3 is the column you are searching in, and 0 means you are looking for a perfect match.

D2 will output the index of the value specified in D1, or #N/A if it is not it the list.