Lookup formula help

[Tom]

I will like to create a formula that will bring me the
corresponding year whenever it finds the matching number.
For expamle. My years are in row one. If the number is
found anywhere in my database I will like to see the
corresponding year.

Any advice? Regular Hlookup and Vlookup won't work
because the lookup value is not in the first row.

Thanks in advance.

Tom

 

[Dave O]

Hi, Tom-
I was able to get the results you're looking for (see example), but it
requires that each number in the "pool" is a unique number.

My example looks like this:
Col a b c d
Row
1 2001 2002 2003 2004
2 1 2 20 30
3 3 4 40 50
4 5 6 60 70
5 7 8 80 90
6 9 10 50 110

In cell A8 I entered an array function:
=SUM(IF(A26=C8,A11,0))

An array function makes Excel work as if it was a query pulling data
from a database. You invoke a typical function in Excel by typing the
formula and pressing the Enter key; invoke an array function by typing
the formula and simultaneously pressing the [Ctrl] [Shift] [Enter]
keys. When you view that cell you'll notice curly brackets surround
the formula; they appear to indicate the array function. You may NOT
type the curly brackets.

Enter the number you're looking for in cell C8. If the number in cell
C8 is among the numbers in A26, the formula returns the year from
row 1.

Contact me at (e-mail address removed) if I can provide more info.

Dave O