Which Function to Use? Search an Array, Return a Row Value
 

I have an array of cells, B3:J34 on sheet2.

On sheet, 1, column, I have a range of values that can be found in the
array on sheet2. I'd like to put a function on column b, sheet1 to
return the value in row 2, above the searched value from sheet1 ,
column A. Does that make sense?

Basically, I want to search for a value within an array, and return
the value in row 2 when the searched value is located.

Thanks

Which Function to Use? Search an Array, Return a Row Value
 

Assuming that Sheet1!A2 contains the lookup value, try the following
formula that needs to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER...

=INDEX(Sheet2!$B$2:$J$2,MATCH(Sheet1!A2,INDEX(Shee t2!$B$3:$J$34,MIN(IF(Sh
eet2!$B$3:$J$34=Sheet1!A2,ROW(Sheet2!$B$3:$J$34)-ROW(Sheet2!$B$3)+1)),0),
0))

Note that if the source data contains more than one occurrence of the
lookup value, the value in Row 2 corresponding to the first occurrence
will be returned. For example, if H5 and D5 contain the lookup value,
the formula will return the value in H2.

Hope this helps!

In article .com,
wrote:

I have an array of cells, B3:J34 on sheet2.

On sheet, 1, column, I have a range of values that can be found in the
array on sheet2. I'd like to put a function on column b, sheet1 to
return the value in row 2, above the searched value from sheet1 ,
column A. Does that make sense?

Basically, I want to search for a value within an array, and return
the value in row 2 when the searched value is located.

Thanks