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

[mark.wolven]

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

 

[Domenic]

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(Sheet2!$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!