Excel 2007 : Match for a value in an array (60x40) and return the row no. of the value found

[nipun]

Hi,

I'm trying to find a formula that can find a value in a specified array and return the row no. of the value found.

Example:

In Sheet 1:
I have an array of texts (A1:AN60) where cell D21 contains the text "olive"

Sheet2:
Cell A1 has the value "olive"
What formula should I apply in Cell B1 (without VBA) that can be applied on the above specified array (in sheet 1) to return the row number of the match found i.e. returns the number 21

Thanks in advance!!

Regards
Nipun

[NBVC]

Try:

=SUMPRODUCT((Sheet1!A1:AN60=A1)*(ROW(Sheet1!A1:AN60)-ROW(Sheet1!A1)+1))

[nipun]

Worked!

Thanks a lot for the quick response )

[nipun]

This formula is giving incorrect result for a few arguments..not able to figure out why

[NBVC]

If you have duplicates in the database it will give incorrect results....